Unit: 1 BASIC CONCPETS OF DBMS

1. Discuss the Purpose of database Systems, use of DBMS

and application of DBMS.
The Database Management System (DBMS) is defined as a software system that
allows the user to define, create and maintain the database and provide control access
to the data.
It is a collection of programs used for managing data and simultaneously it supports
different types of users to create, manage, retrieve, update and store information.
Purpose
The purpose of DBMS is to transform the following −
 Data into information.
 Information into knowledge.
 Knowledge to the action.
The diagram given below explains the process as to how the transformation of data to
information to knowledge to action happens respectively in the DBMS −

Previously, the database applications were built directly on top of the file system.
Drawbacks in File System
There are so many drawbacks in using the file system. These are mentioned below −
 Data redundancy and inconsistency: Different file formats, duplication of
information in different files.
 Difficulty in accessing data: To carry out new task we need to write a new
program.
 Data Isolation − Different files and formats.
 Integrity problems.
 Atomicity of updates − Failures leave the database in an inconsistent state. For
example, the fund transfer from one account to another may be incomplete.
 Concurrent access by multiple users.
 Security problems.
Database system offer so many solutions to all these problems
Uses of DBMS
The main uses of DBMS are as follows −
 Data independence and efficient access of data.
 Application Development time reduces.
 Security and data integrity.
 Uniform data administration.
 Concurrent access and recovery from crashes.
Applications of DBMS
The different applications of DBMS are as follows −
 Railway Reservation System − It is used to keep record of booking of tickets,
departure of the train and the status of arrival and give updates to the
passengers with the help of a database.
 Library Management System − There will be so many numbers of books in the
library and it is very hard to keep a record of all the books in a register or a copy.
So, DBMS is necessary to keep track of all the book records, issue dates, name
of the books, author and maintain the records.
 Banking − We are doing a lot of transactions daily without directly going to the
banks. The only reason is the usage of databases and it manages all the data of
the customers over the database.
  Educational Institutions − All the examinations and the data related to the
students maintained over the internet with the help of a database management
system. It contains registration details of the student, results, grades and courses
available. All these works can be done online without visiting an institution.
 Social Media Websites − By filling the required details we are able to access
social media platforms. Many users daily sign up for social websites such as
Facebook, Pinterest and Instagram. All the information related to the users are
stored and maintained with the help of DBMS.

2. Explain Data abstraction

Ans: Data Abstraction is a process of hiding unwanted or irrelevant details from the
end user. It provides a different view and helps in achieving data independence which is
used to enhance the security of data.
The database systems consist of complicated data structures and relations. For users to
access the data easily, these complications are kept hidden, and only the relevant part
of the database is made accessible to the users through data abstraction.

Levels of abstraction for DBMS

Database systems include complex data-structures. In terms of retrieval of data, reduce
complexity in terms of usability of users and in order to make the system efficient,
developers use levels of abstraction that hide irrelevant details from the users. Levels of
abstraction simplify database design.
Mainly there are three levels of abstraction for DBMS, which are as follows −

 Physical or Internal Level

 Logical or Conceptual Level
 View or External Level
These levels are shown in the diagram below –
Physical or Internal Level
It is the lowest level of abstraction for DBMS which defines how the data is actually
stored, it defines data-structures to store data and access methods used by the
database. Actually, it is decided by developers or database application programmers
how to store the data in the database.
So, overall, the entire database is described in this level that is physical or internal level.
It is a very complex level to understand. For example, customer's information is stored
in tables and data is stored in the form of blocks of storage such as bytes, gigabytes
etc.

Logical or Conceptual Level

Logical level is the intermediate level or next higher level. It describes what data is
stored in the database and what relationship exists among those data. It tries to
describe the entire or whole data because it describes what tables to be created and
what are the links among those tables that are created.
It is less complex than the physical level. Logical level is used by developers or
database administrators (DBA). So, overall, the logical level contains tables (fields and
attributes) and relationships among table attributes.

View or External Level

It is the highest level. In view level, there are different levels of views and every view
only defines a part of the entire data. It also simplifies interaction with the user and it
provides many views or multiple views of the same database.
View level can be used by all users (all levels' users). This level is the least complex
and easy to understand.
For example, a user can interact with a system using GUI that is view level and can
enter details at GUI or screen and the user does not know how data is stored and what
data is stored, this detail is hidden from the user.

Q3. Discuss Database Users

Ans: Database users are the one who really use and take the benefits of database.
There will be different types of users depending on their need and way of accessing the
database.
Application Programmers - They are the developers who interact with the database
by means of DML queries. These DML queries are written in the application programs
like C, C++, JAVA, Pascal [Link] queries are converted into object code to
communicate with the database.
For example, writing a C program to generate the report of employees who are working
in particular department will involve a query to fetch the data from database. It will
include a embedded SQL query in the C Program.
Sophisticated Users - They are database developers, who write SQL queries to
select/insert/delete/update data. They do not use any application or programs to request
the database. They directly interact with the database by means of query language like
SQL. These users will be scientists, engineers, analysts who thoroughly study SQL and
DBMS to apply the concepts in their requirement. In short, we can say this category
includes designers and developers of DBMS and SQL.
Specialized Users - These are also sophisticated users, but they write special
database application programs. They are the developers who develop the complex
programs to the requirement.
Stand-alone Users - These users will have stand –alone database for their personal
use. These kinds of database will have readymade database packages which will have
menus and graphical interfaces.
Native Users - these are the users who use the existing application to interact with the
database. For example, online library system, ticket booking systems, ATMs etc. which
has existing application and users use them to interact with the database to fulfil their
requests.
Q4: Explain DDL in DBMS
Ans: A data definition language (DDL) is a computer language used to create
and modify the structure of database objects in a database. These database
objects include views, schemas, tables, indexes, etc.
Commonly used DDL in SQL querying are CREATE, ALTER, DROP, and
TRUNCATE.

Create
This command builds a new table and has a predefined syntax. The CREATE
statement syntax is:

CREATE TABLE [table name] ([column definitions]) [table parameters];

For example:

CREATE TABLE Employee (Employee Id INTEGER PRIMARY KEY, First name

CHAR (50) NULL, Last name CHAR (75) NOT NULL);

The mandatory semi-colon at the end of the statement is used to process

every command before it. In this example, the string CHAR is used to specify
the data type. Other data types can be DATE, NUMBER, or INTEGER.

Alter
An alter command modifies an existing database table. This command can
add up additional column, drop existing columns and even change the data
type of columns involved in a database table.
An alter command syntax is:

ALTER object type object name parameters;

For example:

ALTER TABLE Employee ADD PRIMARY KEY (employee_pk);

In this example, we added a unique primary key to the table to add a

constraint and enforce a unique value. The constraint “employee_pk” is a
primary key and is on the Employee table.

Drop
A drop command is used to delete objects such as a table, index or view. A
DROP statement cannot be rolled back, so once an object is destroyed,
there’s no way to recover it.

Drop statement syntax is:

DROP object type object name;

For example:

DROP TABLE Employee;

In this example, we’re deleting the Employee table.

Truncate
Similar to DROP, the TRUNCATE statement is used to quickly remove all
records from a table. However, unlike DROP that completely destroys a
table, TRUNCATE preserves its full structure to be reused later.

Truncate statement syntax is:

TRUNCATE TABLE table_name;

For example:

TRUNCATE TABLE Employee;

In this example, we’re marking all the extents of the Employee table for
deallocation, so they’re considered empty for reuse.
 Q.5 Explain Data Dictionary
Ans: A data dictionary contains metadata i.e data about the database. The data
dictionary is very important as it contains information such as what is in the database,
who is allowed to access it, where is the database physically stored etc. The users of
the database normally don't interact with the data dictionary, it is only handled by the
database administrators.
The data dictionary in general contains information about the following −

 Names of all the database tables and their schemas.

 Details about all the tables in the database, such as their owners, their security
constraints, when they were created etc.
 Physical information about the tables such as where they are stored and how.
 Table constraints such as primary key attributes, foreign key information etc.
 Information about the database views that are visible.
This is a data dictionary describing a table that contains employee details.

Field Name Data Field Size for Description Example

Type display

Employee Integer 10 Unique ID of each 1645000001

Number employee

Name Text 20 Name of the employee David

Heston

Date of Birth Date/Time 10 DOB of Employee 08/03/1995

Phone Integer 10 Phone number of 6583648648

Number employee

The different types of data dictionary are −

Active Data Dictionary

If the structure of the database or its specifications change at any point of time, it should
be reflected in the data dictionary. This is the responsibility of the database
management system in which the data dictionary resides.
So, the data dictionary is automatically updated by the database management system
when any changes are made in the database. This is known as an active data dictionary
as it is self updating.

Passive Data Dictionary

This is not as useful or easy to handle as an active data dictionary. A passive data
dictionary is maintained separately to the database whose contents are stored in the
dictionary. That means that if the database is modified the database dictionary is not
automatically updated as in the case of Active Data Dictionary.
So, the passive data dictionary has to be manually updated to match the database. This
needs careful handling or else the database and data dictionary are out of sync.

UNIT:2 DATA MODELS

Q1. DEFINE DATA INDIPANDANCE
Ans: Data independence is the ability to modify the scheme without affecting the
programs and the application to be rewritten. Data is separated from the programs, so
that the changes made to the data will not affect the program execution and the
application.
We know the main purpose of the three levels of data abstraction is to achieve data
independence. If the database changes and expands over time, it is very important that
the changes in one level should not affect the data at other levels of the database. This
would save time and cost required when changing the database.
There are two levels of data independence based on three levels of abstraction. These
are as follows −
 Physical Data Independence
 Logical Data Independence

Physical Data Independence

Physical Data Independence means changing the physical level without affecting the
logical level or conceptual level. Using this property, we can change the storage device
of the database without affecting the logical schema.
The changes in the physical level may include changes using the following −
 A new storage device like magnetic tape, hard disk, etc.
 A new data structure for storage.
  A different data access method or using an alternative files organization
technique.
 Changing the location of the database.

Logical Data Independence

Logical view of data is the user view of the data. It presents data in the form that can be
accessed by the end users.
Codd’s Rule of Logical Data Independence says that users should be able to
manipulate the Logical View of data without any information of its physical storage.
Software or the computer program is used to manipulate the logical view of the data.
Database administrator is the one who decides what information is to be kept in the
database and how to use the logical level of abstraction. It provides the global view of
Data. It also describes what data is to be stored in the database along with the
relationship.
The data independence provides the database in simple structure. It is based on
application domain entities to provide the functional requirement. It provides abstraction
of system functional requirements. Static structure for the logical view is defined in the
class object diagrams. Users cannot manipulate the logical structure of the database.
The changes in the logical level may include −
 Change the data definition.
 Adding, deleting, or updating any new attribute, entity or relationship in the
database.

Q2. Discuss Entity and relationship models

Ans: Entity relationship (ER) models are based on the real-world entities and their
relationships. It is easy for the developers to understand the system by simply looking at
the ER diagram. ER models are normally represented by ER-diagrams.

Components
ER diagram basically having three components:
 Entities − It is a real-world thing which can be a person, place, or even a
concept. For Example: Department, Admin, Courses, Teachers, Students,
Building, etc are some of the entities of a School Management System.
 Attributes − An entity which contains a real-world property called an attribute.
For Example: The entity employee has the property like employee id, salary, age,
etc.
 Relationship − Relationship tells how two attributes are related. For Example:
Employee works for a department.
An entity has a real-world property called attribute and these attributes are defined by a
set of values called domain.
Example 1
In a university,
 A student is an entity,
 University is the database,
 Name and age and sex are the attributes.
 The relationships among entities define the logical association between entities.
Example 2
Given below is another example of ER:

In the above example,

Entities − Employee and Department.
Attributes −
 Employee − Name, id, Age, Salary
 Department − Dept_id, Dept_name
The two entities are connected using the relationship. Here, each employee works for a
department.

Features of ER
The features of ER Model are as follows −
 Graphical Representation is Better Understanding − It is easy and simple to
understand so it can be used by the developers to communicate with the
stakeholders.
 ER Diagram − ER diagrams are used as a visual tool for representing the model.
 Database Design − This model helps the database designers to build the
database.
Advantages
The advantages of ER are as follows −
 The ER model is easy to build.
 This model is widely used by database designers for communicating their ideas.
 This model can easily convert to any other model like network model, hierarchical
model etc.
 It is integrated with the dominant relational model.
Disadvantages
The disadvantages of ER are as follows −
 There is no industry standard for developing an ER model.
 Information might be lost or hidden in the ER model.
 There is no Data Manipulation Language (DML).
 There is limited relationship representation.

Q.3 Explain Entity sets and Relationship sets

Ans: An entity set is a group of entities that posses the same set of
attributes. Each entity in an entity set has its own set of values for
the attributes which make it distinct from other entities in a table. No two
entities in an entity set will have the same values for the attributes.

In a database, an entity set is represented by the Table. Below you can see
the Student Table which as multiple entries i.e. entity. Now, observe that the
two students have the name Jhoson but, still they are uniquely identified as
both posses different roll number.
 Well, in ER diagram an entity set is always represented with the rectangle.
But, an entity can never be represented in ER diagram as it is just
an instance.

Types of Entity Set

Entity set can be classified into two categories as shown below:

1. Strong Entity Set

2. Weak Entity Set

Strong Entity Set

An entity set that has a primary key using which, entities in the table can be
uniquely identified. This kind of entity set is termed as a strong entity set.
Strong entity set is also known as a regular entity set.

Weak Entity Sets

A weak entity set doesn’t have any primary key which can identify each
entity in a set distinctly. But, for discriminating the entities in a set, the weak
entity set is dependent on a particular strong entity set.

A weak entity is also said to be existence dependent as for the existence of

its entities it has to be dependent on identifying entity set i.e. a particular
 ‘strong entity set’. The relation between a weak entity set and a strong entity
set is said to be identifying relationship.

Relationship sets: An entity refers to any object having-

 Either a physical existence such as a particular person, office, house or car.
 Or a conceptual existence such as a school, a university, a company or a job.

In ER diagram,
 Attributes are associated with an entity set.
 Attributes describe the properties of entities in the entity set.
 Based on the values of certain attributes, an entity can be identified uniquely.

Types of Entity Sets-

An entity set may be of the following two types-

1. Strong entity set

2. Weak entity set
Strong Entity Set-

 A strong entity set is an entity set that contains sufficient attributes to uniquely identify all
its entities.
 In other words, a primary key exists for a strong entity set.
 Primary key of a strong entity set is represented by underlining it.

Weak Entity Set-

 A weak entity set is an entity set that does not contain sufficient attributes to uniquely
identify its entities.
 In other words, a primary key does not exist for a weak entity set.
 However, it contains a partial key called as a discriminator.
 Discriminator can identify a group of entities from the entity set.
 Discriminator is represented by underlining with a dashed line.

Q.4) Explain Attributes in DBMS

Ans: In general, an attribute is a characteristic. In a database management

system (DBMS), an attribute refers to a database component, such as a
table.

It also may refer to a database field. Attributes describe the instances in the
column of a database.

It stores only one piece of data about the object represented by the table in
which the attribute belongs. For example, the tuple can be an Invoice entity.
The attributes of an invoice might be Price, Number, Date or Paid/unpaid.

Beyond the self-explanatory simple or single-valued attributes, there are

several types of attributes available.

 Composite attribute: is an attribute composed of several other

simple attributes. For example, the Address attribute of an Employee
entity could consist of the Street, City, Postal code and Country
attributes.

 Multivalued attribute: is an attribute where more than one

description can be provided. For example, an Employee entity may
have more than one Email ID attributes in the same cell.

 Key attribute or primary attribute: is an ID, key, letter or number

that uniquely identifies that item. For example, it can be the number of
a certain invoice (e.g. the individual ID of that invoice). A table that
contains a single key attribute is considered a strong entity. However,
a table might contain more than one key attribute if it’s derived from
other tables.

 Derived attribute: as the name implies, these are derived from other
attributes, either directly or through specific formula results. For
example, the Age attribute of an Employee could be derived from the
 Date of Birth attribute. In other instances, a formula might calculate
the VAT of a certain payment, so that whenever the cell with the
attribute Payment is filled, the cell with the derived attribute VAT
automatically calculates its value.

Q.5 Explain Mapping Constraints in DBMS

o Ans: A mapping constraint is a data constraint that expresses the number of

entities to which another entity can be related via a relationship set.
o It is most useful in describing the relationship sets that involve more than two
entity sets.
o For binary relationship set R on an entity set A and B, there are four possible
mapping cardinalities. These are as follows:
1. One to one (1:1)
2. One to many (1:M)
3. Many to one (M:1)
4. Many to many (M:M)

One-to-one
In one-to-one mapping, an entity in E1 is associated with at most one entity in E2, and
an entity in E2 is associated with at most one entity in E1.

One-to-many
In one-to-many mapping, an entity in E1 is associated with any number of entities in E2,
and an entity in E2 is associated with at most one entity in E1.
Many-to-one
In one-to-many mapping, an entity in E1 is associated with at most one entity in E2, and
an entity in E2 is associated with any number of entities in E1.

Many-to-many
In many-to-many mapping, an entity in E1 is associated with any number of entities in
E2, and an entity in E2 is associated with any number of entities in E1.
Q6: Explain ER- Diagram

Ans: ER model
o ER model stands for an Entity-Relationship model. It is a high-level data model. This
model is used to define the data elements and relationship for a specified system.
o It develops a conceptual design for the database. It also develops a very simple and easy
to design view of data.
o In ER modeling, the database structure is portrayed as a diagram called an entity-
relationship diagram.

For example, Suppose we design a school database. In this database, the student will
be an entity with attributes like address, name, id, age, etc. The address can be another
entity with attributes like city, street name, pin code, etc and there will be a relationship
between them.

Component of ER Diagram
1. Entity:
An entity may be any object, class, person or place. In the ER diagram, an entity can be
represented as rectangles.

Consider an organization as an example- manager, product, employee, department etc.

can be taken as an entity.
a. Weak Entity

3.4M

83

Elon Musk Becomes Twitter's Biggest Shareholder With $2.9 Billion Stock Purchase

An entity that depends on another entity called a weak entity. The weak entity doesn't
contain any key attribute of its own. The weak entity is represented by a double
rectangle.

2. Attribute
The attribute is used to describe the property of an entity. Eclipse is used to represent
an attribute.

For example, id, age, contact number, name, etc. can be attributes of a student.
a. Key Attribute

The key attribute is used to represent the main characteristics of an entity. It represents
a primary key. The key attribute is represented by an ellipse with the text underlined.

b. Composite Attribute
An attribute that composed of many other attributes is known as a composite attribute.
The composite attribute is represented by an ellipse, and those ellipses are connected
with an ellipse.

c. Multivalued Attribute

An attribute can have more than one value. These attributes are known as a multivalued
attribute. The double oval is used to represent multivalued attribute.

For example, a student can have more than one phone number.

d. Derived Attribute

An attribute that can be derived from other attribute is known as a derived attribute. It
can be represented by a dashed ellipse.
For example, A person's age changes over time and can be derived from another
attribute like Date of birth.

3. Relationship
A relationship is used to describe the relation between entities. Diamond or rhombus is
used to represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship

When only one instance of an entity is associated with the relationship, then it is known
as one to one relationship.

For example, A female can marry to one male, and a male can marry to one female.
b. One-to-many relationship

When only one instance of the entity on the left, and more than one instance of an
entity on the right associates with the relationship then this is known as a one-to-many
relationship.

For example, Scientist can invent many inventions, but the invention is done by the
only specific scientist.

c. Many-to-one relationship

When more than one instance of the entity on the left, and only one instance of an
entity on the right associates with the relationship then it is known as a many-to-one
relationship.

For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship
When more than one instance of the entity on the left, and more than one instance of
an entity on the right associates with the relationship then it is known as a many-to-
many relationship.

For example, Employee can assign by many projects and project can have many
employees.

7. Define and discus data model and its types

Ans: A Database model defines the logical design and structure of a

database and defines how data will be stored, accessed and updated
in a database management system. While the Relational Model is the
most widely used database model, there are other models too:

 Hierarchical Model

 Network Model

 Entity-relationship Model

 Relational Model

Hierarchical Model

This database model organises data into a tree-like-structure, with a

single root, to which all the other data is linked. The heirarchy starts
from the Root data, and expands like a tree, adding child nodes to the
parent nodes.
In this model, a child node will only have a single parent node.

This model efficiently describes many real-world relationships like

index of a book, recipes etc.

In hierarchical model, data is organised into tree-like structure with

one one-to-many relationship between two different types of data, for
example, one department can have many courses, many professors
and of-course many students.

Network Model

This is an extension of the Hierarchical model. In this model data is

organised more like a graph, and are allowed to have more than one
parent node.

In this database model data is more related as more relationships are

established in this database model. Also, as the data is more related,
hence accessing the data is also easier and fast. This database model
was used to map many-to-many data relationships.

This was the most widely used database model, before Relational
Model was introduced.

Entity-relationship Model
In this database model, relationships are created by dividing object of
interest into entity and its characteristics into attributes.

Different entities are related using relationships.

E-R Models are defined to represent the relationships into pictorial

form to make it easier for different stakeholders to understand.

This model is good to design a database, which can then be turned

into tables in relational model(explained below).

Let's take an example, If we have to design a School Database,

then Student will be an entity with attributes name, age, address etc.
As Address is generally complex, it can be
another entity with attributes street name, pincode, city etc, and
there will be a relationship between them.

Relationships can also be of different types. To learn about E-R

Diagrams in details, click on the link.

Relational Model

In this model, data is organised in two-dimensional tables and the

relationship is maintained by storing a common field.

This model was introduced by E.F Codd in 1970, and since then it has
been the most widely used database model, infact, we can say the only
database model used around the world.
The basic structure of data in the relational model is tables. All the
information related to a particular type is stored in rows of that table.

Hence, tables are also known as relations in relational model.

In the coming tutorials we will learn how to design tables, normalize

them to reduce data redundancy and how to use Structured Query
language to access data from tables.
 Unit:3 RELATIONAL DATABASE
Relational Algebra is procedural query language, which takes Relation as input
and generate relation as output. Relational algebra mainly provides theoretical
foundation for relational databases and SQL.
Operators in Relational Algebra
Projection (π)
Projection is used to project required column data from a relation.
Example :
R
(A B C)
----------
1 2 4
2 2 3
3 2 3
4 3 4
π (BC)
B C
-----
2 4
2 3
3 4
Note: By Default projection removes duplicate data.

Selection (σ)
Selection is used to select required tuples of the relations.
for the above relation
σ (c>3)R
will select the tuples which have c more than 3.
Note: selection operator only selects the required tuples but does not display
them. For displaying, data projection operator is used.
For the above selected tuples, to display we need to use projection also.
π (σ (c>3)R ) will show following tuples.
A B C
-------
1 2 4
4 3 4

Union (U)
Union operation in relational algebra is same as union operation in set theory,
only constraint is for union of two relation both relation must have same set of
Attributes.

Set Difference (-)

Set Difference in relational algebra is same set difference operation as in set
theory with the constraint that both relation should have same set of attributes.

Rename (ρ)
Rename is a unary operation used for renaming attributes of a relation.
ρ (a/b)R will rename the attribute ‘b’ of relation by ‘a’.

Cross Product (X)

Cross product between two relations let say A and B, so cross product between
A X B will results all the attributes of A followed by each attribute of B. Each
record of A will pairs with every record of B.
below is the example
A B
(Name Age Sex ) (Id Course)
------------------ -------------
Ram 14 M 1 DS
Sona 15 F 2 DBMS
kim 20 M

A X B
Name Age Sex Id Course
---------------------------------
 Ram 14 M 1 DS
Ram 14 M 2 DBMS
Sona 15 F 1 DS
Sona 15 F 2 DBMS
Kim 20 M 1 DS
Kim 20 M 2 DBMS
Note: if A has ‘n’ tuples and B has ‘m’ tuples then A X B will have ‘n*m’ tuples.

Natural Join (⋈)

Natural join is a binary operator. Natural join between two or more relations will
result set of all combination of tuples where they have equal common attribute.
Let us see below example

Emp Dep
(Name Id Dept_name ) (Dept_name Manager)
------------------------ ---------------------
A 120 IT Sale Y
B 125 HR Prod Z
C 110 Sale IT A
D 111 IT

Emp ⋈ Dep

Name Id Dept_name Manager

-------------------------------
A 120 IT A
C 110 Sale Y
D 111 IT A

Conditional Join
Conditional join works similar to natural join. In natural join, by default condition
is equal between common attribute while in conditional join we can specify the
any condition such as greater than, less than, not equal
Let us see below example
R S
(ID Sex Marks) (ID Sex Marks)
------------------ --------------------
1 F 45 10 M 20
2 F 55 11 M 22
3 F 60 12 M 59

Join between R And S with condition [Link] >= [Link]

[Link] [Link] [Link] [Link] [Link] [Link]

-----------------------------------------------
1 F 45 10 M 20
1 F 45 11 M 22
2 F 55 10 M 20
2 F 55 11 M 22
3 F 60 10 M 20
3 F 60 11 M 22
3 F 60 12 M 59
 Unit:4 NORMALIZATION IN RELATIONAL SYSTEM

1. Define and discuss funcational

dependency.

Ans: The functional dependency is a relationship that exists between two attributes.
It typically exists between the primary key and non-key attribute within a table.

1. X → Y

The left side of FD is known as a determinant, the right side of the production is known
as a dependent.

For example:

Assume we have an employee table with attributes: Emp_Id, Emp_Name, Emp_Address.

Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table
because if we know the Emp_Id, we can tell that employee name associated with it.

Functional dependency can be written as:

1. Emp_Id → Emp_Name

We can say that Emp_Name is functionally dependent on Emp_Id.

Types of Functional dependency

 1. Trivial functional dependency
o A → B has trivial functional dependency if B is a subset of A.
o The following dependencies are also trivial like: A → A, B → B

Example:

1. Consider a table with two columns Employee_Id and Employee_Name.

2. {Employee_id, Employee_Name} → Employee_Id is a trivial functional dependency as
3. Employee_Id is a subset of {Employee_Id, Employee_Name}.
4. Also, Employee_Id → Employee_Id and Employee_Name → Employee_Name are trivial depen
dencies too.

2. Non-trivial functional dependency

o A → B has a non-trivial functional dependency if B is not a subset of A.
o When A intersection B is NULL, then A → B is called as complete non-trivial.

Example:

1. ID → Name,
2. Name → DOB
Q.2 Discuss Loss less Join
Ans: Lossless-join decomposition is a process in which a relation is decomposed into
two or more relations. This property guarantees that the extra or less tuple generation
problem does not occur and no information is lost from the original relation during the
decomposition. It is also known as non-additive join decomposition.
When the sub relations combine again then the new relation must be the same as the
original relation was before decomposition.
Consider a relation R if we decomposed it into sub-parts relation R1 and relation R2.
The decomposition is lossless when it satisfies the following statement −
 If we union the sub Relation R1 and R2 then it must contain all the attributes that
are available in the original relation R before decomposition.
 Intersections of R1 and R2 cannot be Null. The sub relation must contain a
common attribute. The common attribute must contain unique data.
The common attribute must be a super key of sub relations either R1 or R2.
Here,
R = (A, B, C)
R1 = (A, B)
R2 = (B, C)
The relation R has three attributes A, B, and C. The relation R is decomposed into two
relation R1 and R2. . R1 and R2 both have 2-2 [Link] common attributes are B.
The Value in Column B must be unique. if it contains a duplicate value then the
Lossless-join decomposition is not possible.
Draw a table of Relation R with Raw Data −
R (A, B, C)

A B C

12 25 34

10 36 09

12 42 30
It decomposes into the two sub relations −
R1 (A, B)

A B

12 25

10 36

12 42

R2 (B, C)

B C

25 34

36 09

42 30

Now, we can check the first condition for Lossless-join decomposition.

The union of sub relation R1 and R2 is the same as relation R.
R1U R2 = R
We get the following result −

A B C

12 25 34

10 36 09
 A B C

12 42 30

The relation is the same as the original relation R. Hence, the above decomposition is
Lossless-join decomposition.

Q.3 Discuss the importance of Normalization

Ans: Normalization is a process to eliminate the flaws of a database with bad design. A
poorly designed database is inconsistent and create issues while adding, deleting or
updating information.
The following makes Database Normalization a crucial step in database design process
−

Resolving the database anomalies

The forms of Normalization i.e. 1NF, 2NF, 3NF, BCF, 4NF and 5NF remove all the
Insert, Update and Delete anomalies.
Insertion Anomaly occurs when you try to insert data in a record that does not exist.
Deletion Anomaly is when a data is to be deleted and due to the poor deign of
database, other record also deletes.

Eliminate Redundancy of Data

Storing same data item multiple times is known as Data Redundancy. A normalized
table do not have the issue of redundancy of data.

Data Dependency
The data gets stored in the correct table and ensures normalization.

Isolation of Data
A good designed database states that the changes in one table or field do not affect
other. This is achieved through Normalization.

Data Consistency
While updating if a record is left, it can led to inconsistent data, Normalization resolves it
and ensures Data Consistency.
Q.4 Compare First second, third BCNF normal forms

Ans: Normalization is the process of minimizing redundancy from a relation

or set of relations. Redundancy in relation may cause insertion, deletion, and
update anomalies. So, it helps to minimize the redundancy in relations. Normal
forms are used to eliminate or reduce redundancy in database tables.

1. First Normal Form –

If a relation contain composite or multi-valued attribute, it violates first normal

form or a relation is in first normal form if it does not contain any composite or
multi-valued attribute. A relation is in first normal form if every attribute in that
relation is singled valued attribute.
 Example 1 – Relation STUDENT in table 1 is not in 1NF because of multi-
valued attribute STUD_PHONE. Its decomposition into 1NF has been shown
in table 2.

 Example 2 –


 ID Name Courses
 ------------------
 1 A c1, c2
 2 E c3
 3 M C2, c3
In the above table Course is a multi-valued attribute so it is not in 1NF.
Below Table is in 1NF as there is no multi-valued attribute
ID Name Course
------------------
1 A c1
1 A c2
2 E c3
3 M c2
3 M c3

2. Second Normal Form –

To be in second normal form, a relation must be in first normal form and relation
must not contain any partial dependency. A relation is in 2NF if it has No Partial
Dependency, i.e., no non-prime attribute (attributes which are not part of any
candidate key) is dependent on any proper subset of any candidate key of the
table.
Partial Dependency – If the proper subset of candidate key determines non-
prime attribute, it is called partial dependency.
 Example 1 – Consider table-3 as following below.
 STUD_NO COURSE_NO COURSE_FEE
 1 C1 1000
 2 C2 1500
 1 C4 2000
 4 C3 1000
 4 C1 1000
 2 C5 2000
{Note that, there are many courses having the same course fee. }
Here,
COURSE_FEE cannot alone decide the value of COURSE_NO or
STUD_NO;
COURSE_FEE together with STUD_NO cannot decide the value of
 COURSE_NO;
COURSE_FEE together with COURSE_NO cannot decide the value of
STUD_NO;
Hence,
COURSE_FEE would be a non-prime attribute, as it does not belong to the
one only candidate key {STUD_NO, COURSE_NO} ;
But, COURSE_NO -> COURSE_FEE, i.e., COURSE_FEE is dependent on
COURSE_NO, which is a proper subset of the candidate key. Non-prime
attribute COURSE_FEE is dependent on a proper subset of the candidate
key, which is a partial dependency and so this relation is not in 2NF.
To convert the above relation to 2NF,
we need to split the table into two tables such as :
Table 1: STUD_NO, COURSE_NO
Table 2: COURSE_NO, COURSE_FEE
Table 1 Table 2
STUD_NO COURSE_NO COURSE_NO
COURSE_FEE
1 C1 C1
1000
2 C2 C2
1500
1 C4 C3
1000
4 C3 C4
2000
4 C1 C5
2000
2 C5
NOTE: 2NF tries to reduce the redundant data getting stored in memory. For
instance, if there are 100 students taking C1 course, we don’t need to store
its Fee as 1000 for all the 100 records, instead, once we can store it in the
second table as the course fee for C1 is 1000.
 Example 2 – Consider following functional dependencies in relation R (A, B
, C, D )
 AB -> C [A and B together determine C]
BC -> D [B and C together determine D]
In the above relation, AB is the only candidate key and there is no partial
dependency, i.e., any proper subset of AB doesn’t determine any non-prime
attribute.
3. Third Normal Form –

A relation is in third normal form, if there is no transitive dependency for

non-prime attributes as well as it is in second normal form.
A relation is in 3NF if at least one of the following condition holds in
every non-trivial function dependency X –> Y
1. X is a super key.
2. Y is a prime attribute (each element of Y is part of some candidate key).

Transitive dependency – If A->B and B->C are two FDs then A->C is called
transitive dependency.
 Example 1 – In relation STUDENT given in Table 4,
FD set: {STUD_NO -> STUD_NAME, STUD_NO -> STUD_STATE,
STUD_STATE -> STUD_COUNTRY, STUD_NO -> STUD_AGE}
Candidate Key: {STUD_NO}
For this relation in table 4, STUD_NO -> STUD_STATE and
STUD_STATE -> STUD_COUNTRY are true. So
STUD_COUNTRY is transitively dependent on STUD_NO. It
violates the third normal form. To convert it in third normal form, we
will decompose the relation STUDENT (STUD_NO, STUD_NAME,
STUD_PHONE, STUD_STATE, STUD_COUNTRY_STUD_AGE)
as:
STUDENT (STUD_NO, STUD_NAME, STUD_PHONE,
STUD_STATE, STUD_AGE)
STATE_COUNTRY (STATE, COUNTRY)
 Example 2 – Consider relation R(A, B, C, D, E)
A -> BC,
CD -> E,
B -> D,
E -> A
All possible candidate keys in above relation are {A, E, CD, BC} All
attributes are on right sides of all functional dependencies are
prime.
4. Boyce-Codd Normal Form (BCNF) –

A relation R is in BCNF if R is in Third Normal Form and for every

FD, LHS is super key. A relation is in BCNF iff in every non-trivial
functional dependency X –> Y, X is a super key.
 Example 1 – Find the highest normal form of a relation
R(A,B,C,D,E) with FD set as {BC->D, AC->BE, B->E}
Step 1. As we can see, (AC)+ ={A,C,B,E,D} but none of its
subset can determine all attribute of relation, So AC will
be candidate key. A or C can’t be derived from any other
attribute of the relation, so there will be only 1 candidate
key {AC}.
Step 2. Prime attributes are those attributes that are part
of candidate key {A, C} in this example and others will be
non-prime {B, D, E} in this example.
Step 3. The relation R is in 1st normal form as a relational
DBMS does not allow multi-valued or composite attribute.
The relation is in 2nd normal form because BC->D is in
2nd normal form (BC is not a proper subset of candidate
key AC) and AC->BE is in 2nd normal form (AC is
candidate key) and B->E is in 2nd normal form (B is not a
proper subset of candidate key AC).
The relation is not in 3rd normal form because in BC->D
(neither BC is a super key nor D is a prime attribute) and
in B->E (neither B is a super key nor E is a prime
attribute) but to satisfy 3rd normal for, either LHS of an FD
should be super key or RHS should be prime attribute.
So the highest normal form of relation will be 2nd Normal
form.
 Example 2 –For example consider relation R(A, B, C)
A -> BC,
B ->
A and B both are super keys so above relation is in BCNF.
Key Points –
3. BCNF is free from redundancy.
4. If a relation is in BCNF, then 3NF is also satisfied.
5. If all attributes of relation are prime attribute, then the relation is
always in 3NF.
6. A relation in a Relational Database is always and at least in 1NF
form.
7. Every Binary Relation ( a Relation with only 2 attributes ) is
always in BCNF.
8. If a Relation has only singleton candidate keys( i.e. every
candidate key consists of only 1 attribute), then the Relation is
always in 2NF( because no Partial functional dependency
possible).
9. Sometimes going for BCNF form may not preserve functional
dependency. In that case go for BCNF only if the lost FD(s) is
not required, else normalize till 3NF only.
10. There are many more Normal forms that exist after BCNF,
like 4NF and more. But in real world database systems it’s
generally not required to go beyond BCNF.

Exercise 1: Find the highest normal form in R (A, B, C, D, E) under

following functional dependencies.
ABC --> D
CD --> AE
Important Points for solving above type of question.
1) It is always a good idea to start checking from BCNF, then 3 NF,
and so on.
2) If any functional dependency satisfied a normal form then there
is no need to check for lower normal form. For example, ABC –> D
is in BCNF (Note that ABC is a superkey), so no need to check this
dependency for lower normal forms.
Candidate keys in the given relation are {ABC, BCD}
BCNF: ABC -> D is in BCNF. Let us check CD -> AE, CD is not a
super key so this dependency is not in BCNF. So, R is not in
BCNF.
3NF: ABC -> D we don’t need to check for this dependency as it
already satisfied BCNF. Let us consider CD -> AE. Since E is not a
prime attribute, so the relation is not in 3NF.
2NF: In 2NF, we need to check for partial dependency. CD is a
proper subset of a candidate key and it determines E, which is non-
prime attribute. So, given relation is also not in 2 NF. So, the
highest normal form is 1 NF.
Unit:5 SQL
Structured Query Language is a standard Database language which is used to
create, maintain and retrieve the relational database. Following are some
interesting facts about SQL.
 SQL is case insensitive. But it is a recommended practice to use keywords
(like SELECT, UPDATE, CREATE, etc) in capital letters and use user
defined things (liked table name, column name, etc) in small letters.
 We can write comments in SQL using “–” (double hyphen) at the beginning
of any line.
 SQL is the programming language for relational databases (explained below)
like MySQL, Oracle, Sybase, SQL Server, Postgre, etc. Other non-relational
databases (also called NoSQL) databases like MongoDB, DynamoDB, etc
do not use SQL
 Although there is an ISO standard for SQL, most of the implementations
slightly vary in syntax. So we may encounter queries that work in SQL
Server but do not work in MySQL.
.
What is Relational Database?
Relational database means the data is stored as well as retrieved in the form
of relations (tables). Table 1 shows the relational database with only one
relation called STUDENT which
stores ROLL_NO, NAME, ADDRESS, PHONE and AGE of students.
STUDENT
ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 9156768971 18

TABLE 1
These are some important terminologies that are used in terms of relation.
Attribute: Attributes are the properties that define a relation.
e.g.; ROLL_NO, NAME etc.
Tuple: Each row in the relation is known as tuple. The above relation
contains 4 tuples, one of which is shown as:
1 RAM DELHI 9455123451 18

Degree: The number of attributes in the relation is known as degree of the

relation. The STUDENT relation defined above has degree 5.
Cardinality: The number of tuples in a relation is known as cardinality.
The STUDENT relation defined above has cardinality 4.
Column: Column represents the set of values for a particular attribute. The
column ROLL_NO is extracted from relation STUDENT.
ROLL_NO

The queries to deal with relational database can be categories as:

Data Definition Language: It is used to define the structure of the
database. e.g; CREATE TABLE, ADD COLUMN, DROP COLUMN and so
on.
Data Manipulation Language: It is used to manipulate data in the relations.
e.g.; INSERT, DELETE, UPDATE and so on.
Data Query Language: It is used to extract the data from the relations. e.g.;
SELECT
So first we will consider the Data Query Language. A generic query to
retrieve from a relational database is:
1. SELECT [DISTINCT] Attribute_List FROM R1,R2….RM
2. [WHERE condition]
3. [GROUP BY (Attributes)[HAVING condition]]
4. [ORDER BY(Attributes)[DESC]];
Part of the query represented by statement 1 is compulsory if you want to
retrieve from a relational database. The statements written inside [] are
optional. We will look at the possible query combination on relation shown in
Table 1.
Case 1: If we want to retrieve attributes ROLL_NO and NAME of all
students, the query will be:
SELECT ROLL_NO, NAME FROM STUDENT;

ROLL_NO NAME

1 RAM

2 RAMESH

3 SUJIT

4 SURESH

Case 2: If we want to retrieve ROLL_NO and NAME of the students

whose ROLL_NO is greater than 2, the query will be:
SELECT ROLL_NO, NAME FROM STUDENT
WHERE ROLL_NO>2;

ROLL_NO NAME

3 SUJIT

4 SURESH

CASE 3: If we want to retrieve all attributes of students, we can write * in

place of writing all attributes as:
SELECT * FROM STUDENT
WHERE ROLL_NO>2;

ROLL_NO NAME ADDRESS PHONE AGE

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 9156768971 18

CASE 4: If we want to represent the relation in ascending order by AGE, we

can use ORDER BY clause as:
SELECT * FROM STUDENT ORDER BY AGE;

ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

4 SURESH DELHI 9156768971 18

3 SUJIT ROHTAK 9156253131 20

Note: ORDER BY AGE is equivalent to ORDER BY AGE ASC. If we want to

retrieve the results in descending order of AGE, we can use ORDER
BY AGE DESC.
CASE 5: If we want to retrieve distinct values of an attribute or group of
attribute, DISTINCT is used as in:
SELECT DISTINCT ADDRESS FROM STUDENT;

ADDRESS

DELHI

GURGAON

ROHTAK

If DISTINCT is not used, DELHI will be repeated twice in result set. Before
understanding GROUP BY and HAVING, we need to understand
aggregations functions in SQL.
AGGRATION FUNCTIONS: Aggregation functions are used to perform
mathematical operations on data values of a relation. Some of the common
aggregation functions used in SQL are:
 COUNT: Count function is used to count the number of rows in a
relation. e.g;
SELECT COUNT (PHONE) FROM STUDENT;

COUNT(PHONE)

SUM: SUM function is used to add the values of an attribute in a

relation. e.g;
SELECT SUM (AGE) FROM STUDENT;
SUM(AGE)

74

In the same way, MIN, MAX and AVG can be used. As we have seen
above, all aggregation functions return only 1 row.
AVERAGE: It gives the average values of the tupples. It is also defined as
sum divided by count values.
Syntax:AVG(attributename)
OR
Syntax:SUM(attributename)/COUNT(attributename)
The above mentioned syntax also retrieves the average value of tupples.
MAXIMUM:It extracts the maximum value among the set of tupples.
Syntax:MAX(attributename)
MINIMUM:It extracts the minimum value amongst the set of all the tupples.
Syntax:MIN(attributename)
GROUP BY: Group by is used to group the tuples of a relation based on an
attribute or group of attribute. It is always combined with aggregation
function which is computed on group. e.g.;
SELECT ADDRESS, SUM(AGE) FROM STUDENT
GROUP BY (ADDRESS);
In this query, SUM(AGE) will be computed but not for entire table but for
each address. i.e.; sum of AGE for address DELHI(18+18=36) and similarly
for other address as well. The output is:
ADDRESS SUM(AGE)

DELHI 36

GURGAON 18

ROHTAK 20

If we try to execute the query given below, it will result in error because
although we have computed SUM(AGE) for each address, there are more
than 1 ROLL_NO for each address we have grouped. So it can’t be
displayed in result set. We need to use aggregate functions on columns after
SELECT statement to make sense of the resulting set whenever we are
using GROUP BY.
SELECT ROLL_NO, ADDRESS, SUM(AGE) FROM STUDENT
GROUP BY (ADDRESS);
NOTE: An attribute which is not a part of GROUP BY clause can’t be used
for selection. Any attribute which is part of GROUP BY CLAUSE can be
used for selection but it is not mandatory. But we could use attributes which
are not a part of the GROUP BY clause in an aggregrate function.

Kindly refer lab manual for SQL unit -5

Unit:6 TRANSACTION PROCESSING
CONCEPTS

Q1. Discuss the idea about Transaction processing in DBMS

Transactions

A transaction is a program including a collection of database operations, executed

as a logical unit of data processing. The operations performed in a transaction
include one or more of database operations like insert, delete, update or retrieve
data. It is an atomic process that is either performed into completion entirely or is
not performed at all. A transaction involving only data retrieval without any data
update is called read-only transaction.
Each high level operation can be divided into a number of low level tasks or
operations. For example, a data update operation can be divided into three tasks −
 read_item() − reads data item from storage to main memory.
 modify_item() − change value of item in the main memory.
 write_item() − write the modified value from main memory to storage.
Database access is restricted to read_item() and write_item() operations. Likewise,
for all transactions, read and write forms the basic database operations.
Q2. Discuss the Transaction and their system concept (Transaction state) in DBMS

Ans:

Transaction Operations

The low level operations performed in a transaction are −

 begin_transaction − A marker that specifies start of transaction execution.
 read_item or write_item − Database operations that may be interleaved
with main memory operations as a part of transaction.
 end_transaction − A marker that specifies end of transaction.
 commit − A signal to specify that the transaction has been successfully
completed in its entirety and will not be undone.
 rollback − A signal to specify that the transaction has been unsuccessful and
so all temporary changes in the database are undone. A committed
transaction cannot be rolled back.
Transaction States

A transaction may go through a subset of five states, active, partially committed,

committed, failed and aborted.
 Active − The initial state where the transaction enters is the active state. The
transaction remains in this state while it is executing read, write or other
operations.
 Partially Committed − The transaction enters this state after the last
statement of the transaction has been executed.
 Committed − The transaction enters this state after successful completion of
the transaction and system checks have issued commit signal.
 Failed − The transaction goes from partially committed state or active state
to failed state when it is discovered that normal execution can no longer
proceed or system checks fail.
 Aborted − This is the state after the transaction has been rolled back after
failure and the database has been restored to its state that was before the
transaction began.
The following state transition diagram depicts the states in the transaction and the
low level transaction operations that causes change in states.

Q3. Discuss the desirable properties of Transaction

Ans:
Desirable Properties of Transactions

Any transaction must maintain the ACID properties, viz. Atomicity, Consistency,
Isolation, and Durability.
 Atomicity − This property states that a transaction is an atomic unit of
processing, that is, either it is performed in its entirety or not performed at
all. No partial update should exist.
 Consistency − A transaction should take the database from one consistent
state to another consistent state. It should not adversely affect any data item
in the database.
 Isolation − A transaction should be executed as if it is the only one in the
system. There should not be any interference from the other concurrent
transactions that are simultaneously running.
 Durability − If a committed transaction brings about a change, that change
should be durable in the database and not lost in case of any failure.

Q.4 Discuss the Schedules types of Schedules

Schedules and Conflicts

In a system with a number of simultaneous transactions, a schedule is the total

order of execution of operations. Given a schedule S comprising of n transactions,
say T1, T2, T3………..Tn; for any transaction Ti, the operations in Ti must
execute as laid down in the schedule S.
Types of Schedules
There are two types of schedules −
 Serial Schedules − In a serial schedule, at any point of time, only one
transaction is active, i.e. there is no overlapping of transactions. This is
depicted in the following graph −
  Parallel Schedules − In parallel schedules, more than one transactions are
active simultaneously, i.e. the transactions contain operations that overlap at
time. This is depicted in the following graph −

Conflicts in Schedules
In a schedule comprising of multiple transactions, a conflict occurs when two
active transactions perform non-compatible operations. Two operations are said to
be in conflict, when all of the following three conditions exists simultaneously −
 The two operations are parts of different transactions.
 Both the operations access the same data item.
 At least one of the operations is a write_item() operation, i.e. it tries to
modify the data item.

Q5. What do you mean by Serializability in DBMS

Ans:

Serializability

A serializable schedule of ‘n’ transactions is a parallel schedule which is

equivalent to a serial schedule comprising of the same ‘n’ transactions. A
serializable schedule contains the correctness of serial schedule while ascertaining
better CPU utilization of parallel schedule.
Equivalence of Schedules
Equivalence of two schedules can be of the following types −
 Result equivalence − Two schedules producing identical results are said to
be result equivalent.
 View equivalence − Two schedules that perform similar action in a similar
manner are said to be view equivalent.
  Conflict equivalence − Two schedules are said to be conflict equivalent if
both contain the same set of transactions and has the same order of
conflicting pairs of operations.

Q6. Discuss the word recoverability in DBMS

Ans: The characteristics of non-serializable schedules are as follows −

 The transactions may or may not be consistent.
 The transactions may or may not be recoverable.
So, now let’s talk about recoverability schedules.
We all know that recoverable and irrecoverable are non-serializable techniques,
Irrecoverable schedules
If a transaction does a dirty read operation from an uncommitted transaction and
commits before the transaction from where it has read the value, then such a
schedule is called an irrecoverable schedule.
Example
Let us consider a two transaction schedules as shown below −

T1 T2

Read(A)

Write(A)

- Read(A) ///Dirty Read

- Write(A)

- Commit
 T1 T2

Rollback

The above schedule is a irrecoverable because of the reasons mentioned below −

 The transaction T2 which is performing a dirty read operation on A.
 The transaction T2 is also committed before the completion of transaction
T2.
 The transaction T1 fails later and there are rollbacks.
 The transaction T2 reads an incorrect value.
 Finally, the transaction T2 cannot recover because it is already committed.
Recoverable Schedules
If any transaction that performs a dirty read operation from an uncommitted
transaction and also its committed operation becomes delayed till the uncommitted
transaction is either committed or rollback such type of schedules is called as
Recoverable Schedules.
Example
Let us consider two transaction schedules as given below −

T1 T2

Read(A)

Write(A)

- Read(A) ///Dirty Read

 T1 T2

- Write(A)

Commit

Commit // delayed

The above schedule is a recoverable schedule because of the reasons mentioned

below −
 The transaction T2 performs dirty read operation on A.
 The commit operation of transaction T2 is delayed until transaction T1
commits or rollback.
 Transaction commits later.
 In the above schedule transaction T2 is now allowed to commit whereas T1
is not yet committed.
 In this case transaction T1 is failed, and transaction T2 still has a chance to
recover by rollback.
Unit: 7 CONCURRENCY CONTROL CONCEPTS

Q1. Define and discuss the concept of concurrency control concept

Ans:

Concurrency can simply be said to be executing multiple transactions at a time. It

is required to increase time efficiency. If many transactions try to access the same
data, then inconsistency arises. Concurrency control required to maintain
consistency data.
For example, if we take ATM machines and do not use concurrency, multiple
persons cannot draw money at a time in different places. This is where we need
concurrency.
Advantages
The advantages of concurrency control are as follows −
 Waiting time will be decreased.
 Response time will decrease.
 Resource utilization will increase.
 System performance & Efficiency is increased.
Control concurrency
The simultaneous execution of transactions over shared databases can create
several data integrity and consistency problems.
For example, if too many people are logging in the ATM machines, serial updates
and synchronization in the bank servers should happen whenever the transaction is
done, if not it gives wrong information and wrong data in the database.
Main problems in using Concurrency
The problems which arise while using concurrency are as follows −
 Updates will be lost − One transaction does some changes and another
transaction deletes that change. One transaction nullifies the updates of
another transaction.
 Uncommitted Dependency or dirty read problem − On variable has
updated in one transaction, at the same time another transaction has started
and deleted the value of the variable there the variable is not getting updated
 or committed that has been done on the first transaction this gives us false
values or the previous values of the variables this is a major problem.
 Inconsistent retrievals − One transaction is updating multiple different
variables, another transaction is in a process to update those variables, and
the problem occurs is inconsistency of the same variable in different
instances.
Concurrency control techniques
The concurrency control techniques are as follows −
Locking
Lock guaranties exclusive use of data items to a current transaction. It first
accesses the data items by acquiring a lock, after completion of the transaction it
releases the lock.
Types of Locks
The types of locks are as follows −
 Shared Lock [Transaction can read only the data item values]
 Exclusive Lock [Used for both read and write data item values]
Time Stamping
Time stamp is a unique identifier created by DBMS that indicates relative starting
time of a transaction. Whatever transaction we are doing it stores the starting time
of the transaction and denotes a specific time.
This can be generated using a system clock or logical counter. This can be started
whenever a transaction is started. Here, the logical counter is incremented after a
new timestamp has been assigned.
Optimistic
It is based on the assumption that conflict is rare and it is more efficient to allow
transactions to proceed without imposing delays to ensure serializability.
Q 2: Discuss the following words in DBMS
Locks, Live Lock, Dead Lock

Locks:

Lock-Based Protocol

In this type of protocol, any transaction cannot read or write data until it acquires
an appropriate lock on it. There are two types of lock:

1. Shared lock:

o It is also known as a Read-only lock. In a shared lock, the data item can only
read by the transaction.
o It can be shared between the transactions because when the transaction holds
a lock, then it can't update the data on the data item.

2. Exclusive lock:

o In the exclusive lock, the data item can be both reads as well as written by
the transaction.
o This lock is exclusive, and in this lock, multiple transactions do not modify
the same data simultaneously.

There are four types of lock protocols available:

1. Simplistic lock protocol

It is the simplest way of locking the data while transaction. Simplistic lock-based
protocols allow all the transactions to get the lock on the data before insert or
delete or update on it. It will unlock the data item after completing the transaction.

19.2M

289

Triggers in SQL (Hindi)

2. Pre-claiming Lock Protocol

o Pre-claiming Lock Protocols evaluate the transaction to list all the data items
on which they need locks.
o Before initiating an execution of the transaction, it requests DBMS for all
the lock on all those data items.
o If all the locks are granted then this protocol allows the transaction to begin.
When the transaction is completed then it releases all the lock.
o If all the locks are not granted then this protocol allows the transaction to
rolls back and waits until all the locks are granted.

3. Two-phase locking (2PL)

o The two-phase locking protocol divides the execution phase of the

transaction into three parts.
o In the first part, when the execution of the transaction starts, it seeks
permission for the lock it requires.
o In the second part, the transaction acquires all the locks. The third phase is
started as soon as the transaction releases its first lock.
o In the third phase, the transaction cannot demand any new locks. It only
releases the acquired locks.
There are two phases of 2PL:

Growing phase: In the growing phase, a new lock on the data item may be
acquired by the transaction, but none can be released.

Shrinking phase: In the shrinking phase, existing lock held by the transaction may
be released, but no new locks can be acquired.

In the below example, if lock conversion is allowed then the following phase can
happen:

1. Upgrading of lock (from S(a) to X (a)) is allowed in growing phase.

2. Downgrading of lock (from X(a) to S(a)) must be done in shrinking phase.

Example:
The following way shows how unlocking and locking work with 2-PL.

Transaction T1:

o Growing phase: from step 1-3

o Shrinking phase: from step 5-7
o Lock point: at 3

Transaction T2:

o Growing phase: from step 2-6

o Shrinking phase: from step 8-9
o Lock point: at 6
4. Strict Two-phase locking (Strict-2PL)

o The first phase of Strict-2PL is similar to 2PL. In the first phase, after
acquiring all the locks, the transaction continues to execute normally.
o The only difference between 2PL and strict 2PL is that Strict-2PL does not
release a lock after using it.
o Strict-2PL waits until the whole transaction to commit, and then it releases
all the locks at a time.
o Strict-2PL protocol does not have shrinking phase of lock release.

1. Introduction

In a multiprogramming environment, more than one process may compete for a

finite set of resources. If a process requests for a resource and the resource is not
presently available, then the process waits for it. Sometimes this waiting process
never succeeds to get access to the resource. This waiting for resources leads to
three scenarios – deadlock, livelock, and starvation.
In this tutorial, we’ll discuss these three conditions.

2. Deadlock

In this section, we’ll first discuss deadlock, its necessary conditions, and how to
prevent it.
2.1. What Is a Deadlock?

A deadlock is a situation in which processes block each other due to resource

acquisition and none of the processes makes any progress as they wait for the
resource held by the other process.

The above figure shows the deadlock scenario between process 1 and process 2.
Both processes are holding one resource and waiting for the other resource held by
the other process. This is a deadlock situation as neither process 1 or process 2 can
make progress until one of the processes gives up its resource.

2.2. Necessary Conditions for Deadlock

To successfully characterize a scenario as deadlock, the following four conditions

must hold simultaneously:
Mutual Exclusion: At least one resource needs to be held by a process in a non-
sharable mode. Any other process requesting that resource needs to wait.
Hold and Wait: A process must hold one resource and requests additional
resources that are currently held by other processes.
No Preemption: A resource can’t be forcefully released from a process. A process
can only release a resource voluntarily once it deems to release.
Circular Wait: A set of a process exists in a manner that is
waiting for a resource held by , waiting for a resource held by .

2.3. How to Prevent Deadlock

To prevent the occurrence of deadlock, at least one of the necessary conditions
discussed in the previous section should not hold true. Let us examine the
possibility of any of these conditions being false:
Mutual Exclusion: In some cases, this condition can be false. For example, in a
read-only file system, one or more processes can be granted sharable access.
However, this condition can’t always be false. The reason being some resources
are intrinsically non-sharable. For instance, a mutex lock is a non-sharable
resource.
Hold and Wait: To ensure that the hold-and-wait condition never occurs, we need
to guarantee that once a process requests for a resource it is not holding any
other resource at that time. In general, a process should request all resources
before it begins its execution.
No Preemption: To make this condition false, a process needs to make sure that it
automatically releases all currently held resources if the newly requested resource
is not available.
Circular Wait: This condition can be made false by imposing a total ordering of
all resource types and ensure that each process requests resources in increasing
order of enumeration. Thus, if there is a set of resources , a process
requires resource and to complete a task, it needs to request first and
then .

3. Livelock

In this section, we’ll discuss live lock which is similar to deadlock with a subtle
difference.

3.1. What Is Livelock?

In the case of a livelock, the states of the processes involved in a live lock scenario
constantly change. On the other hand, the processes still depend on each other
and can never finish their tasks.
The above figure shows an example of livelock. Both “process 1” and “process 2”
need a common resource. Each process checks whether the other process is in an
active state. If so, then it hands over the resource to the other process. However as
both, the process is inactive status, both kept on handing over the resource to each
other indefinitely.
A real-world example of livelock occurs when two people make a telephone call to
each other and both find the line is busy. Both gentlemen decide to hang up and
attempt to call after the same time interval. Thus, in the next retry too, they ended
up in the same situation. This is an example of a live lock as this can go on forever.

3.2. Difference Between Deadlock and Livelock?

Although similar in nature, deadlock, and live locks are not the same. In a
deadlock, processes involved in a deadlock are stuck indefinitely and do not make
any state change. However, in a live lock scenario, processes block each other and
wait indefinitely but they change their resource state continuously. The notable
point is that the resource state change has no effect and does not help the processes
make any progress in their task.
4. Starvation

In this section, we’ll discuss starvation which generally occurs as a result of a

deadlock, livelock, or caused by a greedy process.

4.1. What Is Starvation?

Starvation is an outcome of a process that is unable to gain regular access to the

shared resources it requires to complete a task and thus, unable to make any
progress.

The above figure shows an example of starvation of “process 2” and “process 3”

for the CPU as “process 1” is occupying it for a long duration.

4.2. What Causes Starvation?

Starvation can occur due to deadlock, livelock, or caused by another process.

As we have seen in the event of a deadlock or a live lock a process competes with
another process to acquire the desired resource to complete its task. However, due
to the deadlock or livelock scenario, it failed to acquire the resource and generally
starved for the resource.
Further, it may occur that a process repeatedly gains access to a shared resource or
use it for a longer duration while other processes wait for the same resource. Thus,
the waiting processes are starved for the resource by the greedy process.
4.3. Avoiding Starvation

One of the possible solutions to prevent starvation is to use a resource scheduling

algorithm with a priority queue that also uses the aging technique. Aging is a
technique that periodically increases the priority of a waiting process. With this
approach, any process waiting for a resource for a longer duration eventually gains
a higher priority. And as the resource sharing is driven through the priority of the
process, no process starves for a resource indefinitely.
Another solution to prevent starvation is to follow the round-robin pattern while
allocating the resources to a process. In this pattern, the resource is fairly
allocated to each process providing a chance to use the resource before it is
allocated to another process again.

Q3 What is a serializable and its types

Ans:

A serializable schedule always leaves the database in consistent state. A serial

schedule is always a serializable schedule because in serial schedule, a transaction
only starts when the other transaction finished execution. However a non-serial
schedule needs to be checked for Serializability.

A non-serial schedule of n number of transactions is said to be serializable

schedule, if it is equivalent to the serial schedule of those n transactions. A serial
schedule doesn’t allow concurrency, only one transaction executes at a time and
the other starts when the already running transaction finished.

Types of Serializability

There are two types of Serializability.

1. Conflict Serializability
2. View Serializability
1. Conflict Serializability: In the DBMS Schedules guide, we learned that there are
two types of schedules – Serial & Non-Serial. A Serial schedule doesn’t support
concurrent execution of transactions while a non-serial schedule supports
concurrency. We also learned in Serializability tutorial that a non-serial schedule
may leave the database in inconsistent state so we need to check these non-serial
schedules for the Serializability.

Conflict Serializability is one of the type of Serializability, which can be used to

check whether a non-serial schedule is conflict serializable or not.

2. View Serializability: View Serializability is a process to find out that a

given schedule is view serializable or not.

To check whether a given schedule is view serializable, we need to check whether

the given schedule is View Equivalent to its serial schedule. Lets take an example
to understand what I mean by that.
Unit:8 SECURITY AND INTEGRITY

Q1. Define Authorization in DBMS

Ans: Authorization is finding out if the person once identified, is permitted to have
the resource.
 Authorization explains that what you can do and is handled through the
DBMS unless external security procedures are available.
 This is usually determined by finding out if that person is a part of a
particular group, if that person has paid admission, or has a particular level
of security clearance.
 Authorization is equivalent to checking the guest list at an exclusive party or
checking a ticket in an opera.
 DBMS allows DBA to give different access rights to the users as per their
requirement.
 In SQL Authorization can be done by using Read, Insert, Update or Delete
privileges.
 Types of authorization: We can use any one or combinations of the
following basic forms of authorization.
a) Resource Authorization:

 Authorization to access any system resource

E.g: Sharing of database, Printers.

b) Alternation Authorization:

 Authorization to add attributes or delete attributes from relations.

c) Drop Authorization:

 Authorization to drop a relation.

Database administrator:
 The main authority of databse system is databse administrator(DBA)
  The SQL standard specifies modification to the schema can be done only by
the database owner of schema or DBA of schema.
 The DBA may authorize new users, restructure the database and so on.
 It is analogous to that of superuser or operator for operating systems.
Q.2 define view in DBMS

Ans: A view is a subset of a database that is generated from a user query and gets
stored as a permanent object.

In a structured query language (SQL) database, for example, a view becomes a

type of virtual table with filtered rows and columns that mimic those of the original
database. While the table generated in a view is permanent, the data within fields is
subject to change according to the source database.

Views allow data analysts to focus attention on specific types of information in a

database. They are easy enough to create and save that even a citizen data
scientist can use views to segment a large database into smaller, more manageable
sections for analysis and close-up study.

How a View is Created

A view allows the user to control the amount and specific criteria of the data they
are pulling from a relational database.

For instance, the CUSTOMER_MASTER and ACCOUNTS_MASTER tables in

the relational database of a commercial bank are frequently queried for customers
and their account numbers. The following SQL query returns first name, surname,
account number(s) and account types of customers:

SELECT c.first_name, [Link], a.account_number, a.account_type

FROM customer_master c, accounts_master a

WHERE c.customer_id=a.customer_id

ORDER BY [Link], a.account_number

Under normal circumstances, every time this query is run, it has to be parsed and
loaded into the SQL optimizer. This consumes valuable time and compute
resources.

If the query is saved as a view, however, then overhead activities will only be
performed once at the time the view is created.

The Limitations of View

While views have countless benefits when it comes to working with big databases
in SQL, the view queries tends to fall short in a handful of aspects such as:

 Location Restriction – Both the view and the source database must be in
the same location storage-wise.
 Lack of Compatibility – The user can either use standard SQL or legacy
SQL queries to create a view, but not mix the two.
 Read-only – When views are read-only, the user can perform minor
calculations on data, but the results will have no effect on the original
database.
 Non-synchronized – Although objects in the source database are stored
when a view is created, editing them will not affect the main database. As a
result, future queries may be accurate in relation to the database, but not the
view table and vice-versa.

Q.2 define Security Constraint in DBMS

Ans: SQL constraints are used to specify rules for data in a table.

SQL Create Constraints

Constraints can be specified when the table is created with the CREATE
TABLE statement, or after the table is created with the ALTER TABLE statement.

Syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
 ....
);

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures
the accuracy and reliability of the data in the table. If there is any violation
between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a
column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

 NOT NULL - Ensures that a column cannot have a NULL value

 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely
identifies each row in a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column if no value is specified
 CREATE INDEX - Used to create and retrieve data from the database very
quickly

SQL NOT NULL Constraint

By default, a column can hold NULL values.

The NOT NULL constraint enforces a column to NOT accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert
a new record, or update a record without adding a value to this field.

SQL NOT NULL on CREATE TABLE

The following SQL ensures that the "ID", "LastName", and "FirstName" columns
will NOT accept NULL values when the "Persons" table is created:

Example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

SQL NOT NULL on ALTER TABLE

To create a NOT NULL constraint on the "Age" column when the "Persons" table
is already created, use the following SQL:

ALTER TABLE Persons

MODIFY Age int NOT NULL;
SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for
uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, you can have many UNIQUE constraints per table, but only
one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "ID" column when the
"Persons" table is created:
SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

MySQL:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple

columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "ID" column when the table is already
created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD UNIQUE (ID);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple

columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

MySQL:

ALTER TABLE Persons

DROP INDEX UC_Person;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT UC_Person;
SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can
consist of single or multiple columns (fields).

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "ID" column when the
"Persons" table is created:

MySQL:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY

KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Note: In the example above there is only ONE PRIMARY KEY (PK_Person).
However, the VALUE of the primary key is made up of TWO COLUMNS (ID +
LastName).
SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the "ID" column when the table is
already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD PRIMARY KEY (ID);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY

KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Note: If you use ALTER TABLE to add a primary key, the primary key column(s)
must have been declared to not contain NULL values (when the table was first
created).

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Persons

DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT PK_Person;
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint is used to prevent actions that would destroy links
between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the
primary key is called the referenced or parent table.

Look at the following two tables:

Persons Table

PersonID LastName FirstName

1 Hansen Ola

2 Svendson Tove

3 Pettersen Kari

Orders Table

OrderID OrderNumber PersonID

1 77895 3
2 44678 3

3 22456 2

4 24562 1

Notice that the "PersonID" column in the "Orders" table points to the "PersonID"
column in the "Persons" table.

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the
"Persons" table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders"
table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the
foreign key column, because it has to be one of the values contained in the parent
table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column when the
"Orders" table is created:

MySQL:

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
 PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (

OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN

KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);

SQL FOREIGN KEY on ALTER TABLE

To create a FOREIGN KEY constraint on the "PersonID" column when the

"Orders" table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN
KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Orders

DROP FOREIGN KEY FK_PersonOrder;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders

DROP CONSTRAINT FK_PersonOrder;

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a
column.

If you define a CHECK constraint on a column it will allow only certain values for
this column.

If you define a CHECK constraint on a table it can limit the values in certain
columns based on values in other columns in the row.

SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age" column when the
"Persons" table is created. The CHECK constraint ensures that the age of a person
must be 18, or older:

MySQL:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18)
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on

multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
SQL CHECK on ALTER TABLE

To create a CHECK constraint on the "Age" column when the table is already
created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on

multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT CHK_PersonAge;

MySQL:

ALTER TABLE Persons

DROP CHECK CHK_PersonAge;
SQL DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column.

The default value will be added to all new records, if no other value is specified.
SQL DEFAULT on CREATE TABLE

The following SQL sets a DEFAULT value for the "City" column when the
"Persons" table is created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);

The DEFAULT constraint can also be used to insert system values, by using
functions like GETDATE():

CREATE TABLE Orders (

ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);

SQL DEFAULT on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already
created, use the following SQL:

MySQL:

ALTER TABLE Persons

ALTER City SET DEFAULT 'Sandnes';

SQL Server:
ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;

MS Access:

ALTER TABLE Persons

ALTER COLUMN City SET DEFAULT 'Sandnes';

Oracle:

ALTER TABLE Persons

MODIFY City DEFAULT 'Sandnes';

DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

MySQL:

ALTER TABLE Persons

ALTER City DROP DEFAULT;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ALTER COLUMN City DROP DEFAULT;

SQL Server:

ALTER TABLE Persons

ALTER COLUMN City DROP DEFAULT;

SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise.
The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without
(because the indexes also need an update). So, only create indexes on columns that
will be frequently searched against.
CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);

Note: The syntax for creating indexes varies among different databases. Therefore:
Check the syntax for creating indexes in your database.

CREATE INDEX Example

The SQL statement below creates an index named "idx_lastname" on the

"LastName" column in the "Persons" table:

CREATE INDEX idx_lastname

ON Persons (LastName);

If you want to create an index on a combination of columns, you can list the
column names within the parentheses, separated by commas:

CREATE INDEX idx_pname

ON Persons (LastName, FirstName);
DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

MS Access:

DROP INDEX index_name ON table_name;

SQL Server:

DROP INDEX table_name.index_name;

DB2/Oracle:

DROP INDEX index_name;

MySQL:

ALTER TABLE table_name

DROP INDEX index_name;

Q.3 define Integrity Constraint in DBMS

Ans: Integrity Constraints

o Integrity constraints are a set of rules. It is used to maintain the quality of
information.
 o Integrity constraints ensure that the data insertion, updating, and other
processes have to be performed in such a way that data integrity is not
affected.
o Thus, integrity constraint is used to guard against accidental damage to the
database.

Types of Integrity Constraint

1. Domain constraints

o Domain constraints can be defined as the definition of a valid set of values

for an attribute.
o The data type of domain includes string, character, integer, time, date,
currency, etc. The value of the attribute must be available in the
corresponding domain.

Example:
2. Entity integrity constraints

o The entity integrity constraint states that primary key value can't be null.
o This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those
rows.
o A table can contain a null value other than the primary key field.

Example:

3. Referential Integrity Constraints

o A referential integrity constraint is specified between two tables.

 o In the Referential integrity constraints, if a foreign key in Table 1 refers to
the Primary Key of Table 2, then every value of the Foreign Key in Table 1
must be null or be available in Table 2.

Example:

4. Key constraints

o Keys are the entity set that is used to identify an entity within its entity set
uniquely.
o An entity set can have multiple keys, but out of which one key will be the
primary key. A primary key can contain a unique and null value in the
relational table.

Example:
4. What is Data Encryption in DBMS?
Ans: A DBMS can use encryption to protect information in certain situations
where the normal security mechanisms of the DBMS are not adequate. For
example, an intruder may steal tapes containing some data or tap a communication
line. By storing and transmitting data in an encrypted form, the DBMS ensures that
such stolen data is not intelligible to the intruder. Thus, encryption is a technique to
provide privacy of data.

In encryption, the message to be encrypted is known as plaintext. The plaintext is

transformed by a function that is parameterized by a key. The output of the
encryption process is known as the cipher text. Ciphertext is then transmitted over
the network. The process of converting the plaintext to ciphertext is called as
Encryption and process of converting the ciphertext to plaintext is called as
Decryption. Encryption is performed at the transmitting end and decryption is
performed at the receiving end. For encryption process we need the encryption
key and for decryption process we need decryption key as shown in figure.
Without the knowledge of decryption key intruder cannot break the ciphertext to
plaintext. This process is also called as Cryptography.

The basic idea behind encryption is to apply an encryption algorithm, which may’
be accessible to the intruder, to the original data and a user-specified or DBA-
specified encryption key, ‘which is kept secret. The output of the algorithm is the
encrypted version of the data. There is also a decryption algorithm, which takes
the encrypted data and the decryption key as input and then returns the original
data. Without the correct decryption key, the decryption algorithm produces
gibberish. Encryption and decryption keys may be same or· different but there
must be relation between the both which must me secret.
Techniques used for Encryption

There are following techniques used for encryption process:

• Substitution Ciphers
• Transposition Ciphers
Substitution Ciphers: In a substitution cipher each letter or group of letters is
replaced by another letter or group of letters to mask them For example: a is
replaced with D, b with E, c with F and z with C. In this way attack becomes. The
substitution ciphers are not much secure because intruder can easily guess the
substitution characters.
Transposition Ciphers: Substitution ciphers preserve the order of the plaintext
symbols but mask them-;-The transposition cipher in contrast reorders the letters
but do not mask them. For this process a key is used. For example: A may be
coded as B. The transposition ciphers are more secure as compared to substitution
ciphers.