DATABASE MANAGEMENT SYSTEMS

(23E05402T)

B. TECH II YEAR - II SEM (2024-25)

DEPARTMENT COMPUTER SCIENCE AND ENGINEERINGOF

CHAITANYA BHARATHI INSTITUTE OF TECHNOLOGY

(Autonomous Institution – UGC, Govt. of India)
(Affiliated to AICTE, NEW DELHI, And Affiliated to JNTUA ,Anantapuramu Accredited by NBA & NAAC – ‘A’ Grade, and NBA
and Recognized By UGC) VIDYA NAGAR, Pallavolu ,Proddatur – 516352, Andhra Pradesh, INDIA.
UNIT -1
 DBMS (R23 Syllabus)
UNIT - I
Syllabus: Introduction: Database system, Characteristics (Database Vs File System), Database Users,
Advantages of Database systems, Database applications. Brief introduction of different Data Models; Concepts
of Schema, Instance and data independence; Three tier schema architecture for data independence; Database
system structure, environment, Centralized and Client Server architecture for the database.
Entity Relationship Model: Introduction, Representation of entities, attributes, entity set, relationship,
relationship set, constraints, sub classes, super class, inheritance, specialization, generalization using ER
Diagrams.
Introduction
1.1 Database System
Data: Data is the collection of raw facts. It can be in the form of numbers, text, images, or other types of data.
Information: Processed data is the information.
Database System: The database is the collection of data that is stored in the system. It can be thought of as a
container that holds the data.
Database systems are designed to manage large bodies of information. Management of data involves both
defining structures for storage of information and providing mechanisms for the manipulation of information.
In addition, the database system must ensure the safety of the information stored, despite system crashes or
attempts at unauthorized access. If data are to be shared among several users, the system must avoid possible
anomalous results.
Database Management System (DBMS): The DBMS is the software that manages the database. It provides a
interface between the user and the database, and allows users to create, modify, and query the database.
Or
A database-management system (DBMS) is a collection of interrelated data and a set of programs to access
those data.
1.2 Characteristics (Database Vs File System):
A shared collection of logically related data along with the description of the data that suits to the needs of
large enterprises.

This unit describes the basic differences between the traditional way of processing, also called as file processing,
and the database method of processing the data. Every operating system provides users to open, save, and close
a file. The users can store appropriate information in these files. Take a look at the Figure 1.1 which shows the
traditional file processing system that stores the program and data description in a file. The related information
of a particular application is stored in various files named as File1, File2, etc., and these files are manipulated
using Program1, Program2, etc. This is the method that was used in early days.
It means that without a DBMS, the data will simply be dumped into one or more files. For any updation, the
files need to be opened and manually search for the line or record, update and then save the file. Now you can
understand the difficulties involved in marinating this type of information storage.

With the advent of database systems, the file processing approach is no longer used. Now you can observe with
the Figure 1.2 that the database is in the disk which in turn is controlled by the DBMS. In this approach the
Application Program-1 along with its data semantics, Application Program-2 along with its data semantics, etc.,
interact with the database where the actual data and constraints are stored through the DBMS. The DBMS
provides the necessary control and manipulation software modules for these application programs to access the
data stored in the database.
Drawbacks of File Processing System
1.Catalog: In DBMS, the database structure is stored in a catalog and it also contains the storage details with
constraints.
The DBMS software must equally work with any number of database applications provided the catalog contains
the structure and other details of that application. In file processing the data definition is part of the application
program.
Example: Record declaration in Pascal. Class or structure declaration in C++.
2. Program-data independence: In file processing, if changes are done in the structure of the file, then we may
require changing the program design that accesses it. In DBMS the access programs are written independent of
any specific files. This is called as program-data independence.
The DBMS stores the data in such a way that the user need not be aware of these details. This concept is called
as data abstraction and it may also be called as conceptual representation
3. Views: A database may have many users and each one may be interested on a particular view of the
application. A view is conceptually a table, but the records of this table are not stored in the database.
Example: Consider the Student database in which we can think of two views:
View 1: Students Grade in various courses. To obtain this information the tables Course and Grade_Report are
to be joined and created as a view.
View 2: If we want to know the Prerequisite Courses that a student needs to study, three tables are to be joined.
These tables are nothing but Student, Section and Prerequisite.
4. Sharing and Transaction processing: A DBMS must provide control for various users trying to access the
database.

Example: Railway Reservation System with multiple counters.

Whenever several users try to access the same application at the same time, we call this situation as concurrent
transaction processing. Generally, the concurrent access is achieved with a simple Local Area Network (LAN).
It is also possible to book railway tickets online i.e. through Internet.

1.3 Database users

Database Users and User Interfaces There are four different types of database-system users, differentiated by
the way they expect to interact with the system. Different types of user interfaces have been designed for the
different types of users.
Naive Users are unsophisticated users who interact with the system by invoking one of the application programs
that have been written previously. For example, a clerk in the university who needs to add a new instructor to
department A invokes a program called new hire. This program asks the clerk for the name of the new instructor,
her new ID, the name of the department (that is, A), and the salary. The typical user interface for naive users is
a forms interface, where the user can fill in appropriate fields of the form. Naive users may also simply read
reports generated from the database. As another example, consider a student, who during class registration
period, wishes to register for a class by using a Web interface. Such a user connects to a Web application
program that runs at a Web server. The application first verifies the identity of the user, and allows her to access
a form where she enters the desired information. The form information is sent back to the Web application at
the server, which then determines if there is room in the class (by retrieving information from the database) and
if so adds the student information to the class roster in the database.
Application programmers are computer professionals who write application programs. Application
programmers can choose from many tools to develop user interfaces. Rapid application development (RAD)
tools are tools that enable an application programmer to construct forms and reports with minimal programming
effort.
Sophisticated users interact with the system without writing programs. Instead, they form their requests either
using a database query language or by Apago PDF Enhancer using tools such as data analysis software. Analysts
who submit queries to explore data in the database fall in this category
Specialized users are sophisticated users who write specialized database applications that do not fit into the
traditional data-processing framework. Among these applications are computer-aided design systems,
knowledge base and expert systems, systems that store data with complex data types (for example, graphics
data and audio data), and environment- modelling systems.
Database Administrators: One of the main reasons for using DBMSs is to have central control of both the
data and the programs that access those data. A person who has such central control over the system is called a
database administrator (DBA).
The functions of a DBA include:
• Schema definition. The DBA creates the original database schema by executing a set of data definition
statements in the DDL.
• Storage structure and access-method definition.
• Schema and physical-organization modification. The DBA carries out changes to the schema and physical
organization to reflect the changing needs of the organization, or to alter the physical organization to improve
performance
1.4 Advantages of Database Systems
Data redundancy and inconsistency. Since different programmers create the files and application programs
over a long period, the various files are likely to have different structures and the programs may be written in
several programming languages. Moreover, the same information may be duplicated in several places (files).
For example, if a student has a double major (say, music and mathematics) the address and telephone number
of that student may appear in a file that consists of student records of students in the Music department and in a
file that consists of student records of students in the Mathematics department. This redundancy leads to higher
storage and access cost. In addition, it may lead to data inconsistency; that is, the various copies of the same
data may no longer agree. For example, a changed student address may be reflected in the Music department
records but not elsewhere in the system.
Difficulty in accessing data. Suppose that one of the university clerks needs to find out the names of all students
who live within a particular postal-code Apago PDF Enhancer area. The clerk asks the data-processing
department to generate such a list. Because the designers of the original system did not anticipate this request,
there is no application program on hand to meet it. There is, however, an application program to generate the
list of all students. The university clerk has now two choices: either obtain the list of all students and extract the
needed information manually or ask a programmer to write the necessary application program. Both alternatives
are obviously unsatisfactory. Suppose that such a program is written, and that, several days later, the same clerk
needs to trim that list to include only those students who have taken at least 60 credit hours. As expected, a
program to generate such a list does not exist. Again, the clerk has the preceding two options, neither of which
is satisfactory.
Data isolation. Because data are scattered in various files, and files may be in different formats, writing new
application programs to retrieve the appropriate data is difficult.
• Integrity problems. The data values stored in the database must satisfy certain types of consistency
constraints. Suppose the university maintains an account for each department, and records the balance amount
in each account. Suppose also that the university requires that the account balance of a department may never
fall below zero. Developers enforce these constraints in the system by adding appropriate code in the various
application programs. However, when new constraints are added, it is difficult to change the programs to enforce
them. The problem is compounded when constraints involve several data items from different files.
Atomicity problems. A computer system, like any other device, is subject to failure. In many applications, it is
crucial that, if a failure occurs, the data be restored to the consistent state that existed prior to the failure.
Consider a program to transfer $500 from the account balance of department A to the account balance of
department B. If a system failure occurs during the execution of the program, it is possible that the $500 was
removed from the balance of department A but was not credited to the balance of department B, resulting in an
inconsistent database state. Clearly, it is essential to database consistency that either both the credit and debit
occur, or that neither occur. That is, the funds transfer must be atomic—it must happen in its entirety or not at
all. It is difficult to ensure atomicity in a conventional file-processing system.
Concurrent-access anomalies. For the sake of overall performance of the system and faster response, many
systems allow multiple users to update the data simultaneously. Indeed, today, the largest Internet retailers may
have millions of accesses per day to their data by shoppers. In such an environment, interaction of concurrent
updates is possible and may result in inconsistent data. Consider department A, with an account balance of
$10,000. If two department clerks debit the account balance (by say $500 and $100, re spectively) of department
A at almost exactly the same time, the result of the concurrent executions may leave the budget in an incorrect
(or inconsistent) state. Suppose that the programs executing on behalf of each withdrawal read the old
balance, reduce that value by the amount being withdrawn, and write the result back. If the two programs run
concurrently, they may both read the value $10,000, and write back $9500 and $9900, respectively. Depending
on which one writes the value last, the account balance of department A may contain either $9500 or $9900,
rather than the correct value of $9400. To guard against this possibility, the system must maintain some form
of supervision. But supervision is difficult to provide because data may be accessed by many different
application programs that have not been coordinated previously.
1.5 Database Applications
Databases are widely used. Here are some representative applications:
Enterprise Information
Sales: For customer, product, and purchase information.
Accounting: For payments, receipts, account balances, assets and other accounting information.
Human resources: For information about employees, salaries, payroll taxes, and benefits, and for generation
of pay checks.
Manufacturing: For management of the supply chain and for tracking production of items in factories,
inventories of items in warehouses and stores, and orders for items.
Online retailers: For sales data noted above plus online order tracking, generation of recommendation lists,
and maintenance of online product evaluations.
Banking and Finance
◦ Banking: For customer information, accounts, loans, and banking transac tions. ◦
Credit card transactions: For purchases on credit cards and generation of monthly statements.
◦ Finance: For storing information about holdings, sales, and purchases of financial instruments such as stocks
and bonds; also for storing real-time market data to enable online trading by customers and automated trading
by the firm.
Universities: For student information, course registrations, and grades (in addition to standard enterprise
information such as human resources and accounting).
 Airlines: For reservations and schedule information. Airlines were among the first to use databases in a
geographically distributed manner.
Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on
prepaid calling cards, and storing information Apago PDF Enhancer about the communication networks.

The importance of database systems can be judged in another way—today, database system vendors like
Oracle are among the largest software companies in the world, and database systems form an important part of
the product line of Microsoft and IBM.
1.6 Brief introduction of different Data Models
Underlying the structure of a database is the data model: a collection of conceptual tools for describing data,
data relationships, data semantics, and consistency constraints. A data model provides a way to describe the
design of a database at the physical, logical, and view levels.
There are a number of different data models that we shall cover in the text. The data models can be classified
into four different categories:
• Relational Model. The relational model uses a collection of tables to represent both data and the relationships
among those data. Each table has multiple columns, and each column has a unique name. Tables are also known
as relations. The relational model is an example of a record-based model. Record-based models are so named
because the database is structured in fixed-format records of several types. Each table contains records of a
particular type. Each record type defines a fixed number of fields, or attributes. The columns of the table
correspond to the attributes of the record type. The relational data model is the most widely used data model,
and a vast majority of current database systems are based on the relational model.
The entity-relationship (E-R) data model uses a collection of basic objects, called entities, and relationships
among these objects. An entity is a “thing” or “object” in the real world that is distinguishable from other
objects.
Object-Based Data Model. Object-oriented programming (especiallyinJava, C++, or C#) has become the
dominant software-development methodology. This led to the development of an object-oriented data model
that can be seen as extending the E-R model with notions of encapsulation, methods (functions), and object
identity. The object-relational data model combines features of the object-oriented data model and relational
data model.
• Semistructured Data Model. The semi structured data model permits the specification of data where
individual data items of the same type may have different sets of attributes. This is in contrast to the data models
mentioned earlier, where every data item of a particular type must have the same set of attributes. The Extensible
Markup Language (XML) is widely used to represent semi structured data.
1.7 Concept of Schema
When we talk about a database, we must differentiate between the database schema, which is the logical design
of the database, and the database instance, which is a snapshot of the data in the database at a given instant in
time. The concept of a relation corresponds to the programming-language notion of a variable, while the concept
of a relation schema corresponds to the programming-language notion of type definition. In general, a relation
schema consists of a list of attributes and their corresponding domains.

The schema for that relation is

department (dept name, building, budget)
1.8 Database Instance
It is important that we distinguish these two terms individually. Database schema is the skeleton of database. It
is designed when the database doesn't exist at all. Once the database is operational, it is very difficult to make
any changes to it. A database schema does not contain any data or information.
A database instance is a state of operational database with data at any given time. It contains a snapshot of the
database. Database instances tend to change with time. A DBMS ensures that its every instance (state) is in a
valid state, by diligently following all the validations, constraints, and conditions that the database designers
have imposed.
1.9 Data Independence
A very important advantage of using a DBMS is that it offers data independence. That is, application programs
are insulated from changes in the way the data is structured and stored. Data independence is achieved through
use of the three levels of data abstraction; in particular, the conceptual schema and the external schema provide
distinct benefits in this area.
Relations in the external schema (view relations) are in principle generated on demand from the relations
corresponding to the conceptual schema.4 If the underlying data is reorganized, that is, the conceptual schema
is changed, the definition of a view relation can be modified so that the same relation is computed as before.
For example, suppose that the Faculty relation in our university database is replaced by the following two
relations:
Faculty_public(fid: string, fname: string, office: integer)
Faculty_private (J£d: string, sal: real)
Intuitively, some confidential information about faculty has been placed in a separate relation and information
about offices has been added. The Course info view relation can be redefined in terms of Faculty_public and
Faculty_private, which together contain all the information in Faculty, so that a user who queries Courseinfo
will get the same answers as before.
1.10 Three tier schema architecture for data independence;
A database schema is the skeleton structure that represents the logical view of the entire database. It defines
how the data is organized and how the relations among them are associated. It formulates all the constraints that
are to be applied on the data.
A database schema defines its entities and the relationship among them. It contains a descriptive detail of the
database, which can be depicted by means of schema diagrams. It’s the database designers who design the
schema to help programmers understand the database and make it useful.

A database schema can be divided broadly into two categories –

 Physical Database Schema − this schema pertains to the actual storage of data and its form of storage like
files, indices, etc. It defines how the data will be stored in a secondary storage.
 Logical Database Schema − this schema defines all the logical constraints that need to be applied on the data
stored. It defines tables, views, and integrity constraints.
1.11 Database System Structure and Environment
Storage manager: The storage manager is the component of a database system that provides the interface
between the low-level data stored in the database and the application programs and queries submitted to the
system. The storage manager is responsible for the interaction with the file manager. The raw data are stored
on the disk using the file system provided by the operating system. The storage manager translates the various
DML statements into low-level file-system commands.
Thus, the storage manager is responsible for storing, retrieving, and updating data in the database.
The storage manager components include:
Authorization and integrity manager, which tests for the satisfaction of integrity constraints and checks the
authority of users to access data.
• Transaction manager, which ensures that the database remains in a consis tent (correct) state despite
system failures, and that concurrent transaction executions proceed without conflicting.
• File manager,which manages the allocation of space on disk storage and the data structures used to
represent information stored on disk.
• Buffer manager,which is responsible for fetching data from disk storage into main memory, and deciding
what data to cache in main memory. The buffer manager is acritical part of the database system, since it
enables the database to handle data sizes that are much larger than the size of main memory.
The storage manager implements several data structures as part of the physical system implementation:
• Datafiles, which store the database itself.
• Data dictionary, which stores metadata about the structure of the database, in particular the schema of the
database.
• Indices, which can provide fast access to data items. Like the index in this textbook, a database index provides
pointers to those data items that hold a particular value. For example, we could use an index to find the instructor
record with a particular ID, or all instructor records with a particular name. Hashing is an alternative to indexing
that is faster in some but not all cases
The Query Processor
The query processor components include:
DDL interpreter, which interprets DDL statements and records the definitions in the data dictionary.
DML compiler, which translates DML statements in a query language into an evaluation plan consisting of
low-level instructions that the query evaluation engine understands.
A query can usually be translated into any of a number of alternative evaluation plans that all give the same
result. The DML compiler also performs query optimization; that is, it picks the lowest cost evaluation plan
from among the alternatives.
• Query evaluation engine, which executes low-level instructions generated by the DML compiler.

1.12 Centralized and Client Server architecture for the database.

In a two-tier architecture, the application resides at the client machine, where it invokes database system
functionality at the server machine through query language statements. Application program interface standards like
ODBC and JDBC are used for interaction between the client and the server.

In contrast, in a three-tier architecture, the client machine acts as merely a front end and does not contain any
direct database calls. Instead, the client end communicates with an application server, usually through a forms
interface. The application server in turn communicates with a database system to access data. The business logic
of the application, which says what actions to carry out under what conditions, is embedded in the application
server, instead of being distributed across multiple clients. Three-tier applications are more appropriate for large
applications, and for applications that run on the World Wide Web.
Entity Relationship Model
1.13 Introduction
The Entity-Relationship (ER) Model is a conceptual framework used to describe the structure of a
database in terms of entities, their attributes, and the relationships between them. It was introduced by
Peter Chen in 1976 and is commonly used in the design phase of database systems to visually represent
 the data requirements and relationships before the actual database is created.
Advantages of the ER Model:
 Easy to Understand: The ER model provides an intuitive and easy-to-understand view of data
relationships.
 Comprehensive: It captures all essential aspects of the data model, including entities,
attributes, and relationships.
 Flexible: The model can be adapted to describe complex relationships, including hierarchies and
weak entities.
Limitations:
 Static Representation: The ER model captures data at a specific point in time and doesn't
represent changes or dynamics over time.
 Not Suitable for Some Database Operations: Some database operations, like performance
optimization or indexing, are not covered in the ER model.
1.14 Representation of Entities
Entity: An entity is an object and it is represented by rectangle symbol.
Student College

For example: In the following ER diagram we have two entities Student and College and these two entities
have many to one relationship as many student’s study in a single college.

Student College
study
Types of Entities: An entity is classified into two types. 1. Weak Entity and 2. Strong Entity
1. Weak Entity: An entity that cannot be uniquely identified by its own attributes and relies on
the relationship with other entity is called weak entity. The weak entity is represented by a
double rectangle.

For example – a bank account cannot be uniquely identified without knowing the bank to
which the account belongs, so bank account is a weak entity.

Bank Account Bank

Is opened in

Weak entity Strong entity

1. Strong Entity: A strong entity is an entity that can exist independently and is uniquely
identifiable by its own attributes. It has a primary key that uniquely identifies each entity
instance.
Example: A "Student" entity in a college database, with StudentID as its primary key, is
a strong entity because it doesn’t rely on other entities for identification.

Student College
study

1.15 Attributes:
 Attributes represent the properties or characteristics of an entity.
 For example, for a "Student" entity, attributes might be Name, Student ID, Date of Birth, etc.
 Each attribute is represented by an oval in an ER diagram, connected to its corresponding entity.
Types of attributes:
 Simple Attribute (or) Key Attribute : Cannot be divided further (e.g., Age, ID).
For example, student roll number can uniquely identify
a student from a set of students. Key attribute is
represented by oval same as other attributes however
the text of key attribute is underlined.

 Composite Attribute: Can be divided into

smaller sub-parts (e.g., Full Name can be
divided into First Name and Last Name).
For example, In student entity, the student
address is a composite attribute as an address is
composed of other attributes such as pin code,
state, country.
 Derived Attribute: Value can be derived from other attributes. The
derived attribute denoted by dotted lines. (e.g., Age can be derived from
Date of Birth).
For example – Person age is a derived attribute. The age of person is
derived from Date of Birth and Current Date.

 Multi-valued Attribute: An attribute that can hold multiple values is known as multivalued
attribute. It is represented with double ovals in an ER Diagram. For example – A person can have
more than one phone numbers so the phone number attribute is multivalued. (e.g., Phone
Numbers for a person).

1.16 Entity Sets: An entity set is a group of entities that share the same properties and are used to
organize data in a database.
For example, a customer entity might have attributes like name, address, and phone number. A customer
entity set would be a group of customers with those same attributes.
 Customer
name
Customer_id Customer phone
no

Customer

1.15. Relationship and Relationship set:

Relationship:
 Relationships describe how entities are related to one another.
 For example, a "Customer" places an "Order," or a "Student" enrolls in a "Course."
 Relationships are represented by diamonds in an ER diagram, with lines connecting them to the
related entities.

Relationship set:
In a Relational database, relationship sets are built
up by utilizing keys, such as primary and foreign
keys, to interface related records over distinctive
tables.

Weak Relationship: A weak, or non-

identifying, relationship exists
if the primary key of the related entity does
not contain a primary key component of
the parent entity.

In the above ER Diagram, ‘Payment’ is the weak entity, ‘Loan Payment’ is the non-identifying
relationship and ‘Payment Number’ is the partial key. Primary Key of the Loan along with the partial key
would be used to identify the records.

Strong Relationship: A strong relationship in an

Entity Relationship (ER) diagram is when the
primary key of a related entity contains the
primary key of the parent entity
Types of Relationships:
There are two types of relationships in dbms. They are
1. Degree of Relationship.
2. Cardinality Relationship.
1. Degree of Relationship:
In Database Management Systems (DBMS), the degree of a relationship refers to the number of entity
types (or entity sets) involved in a relationship. The degree of relationship in DBMS can be categorized
as a Unary, Binary, Ternary.
Unary Relationship (Degree 1):
 A unary relationship involves only one entity type that is related to itself.
 This is also known as a recursive relationship, where an entity participates in the relationship with
another entity of the same type.
 Example: In an organization, the
Employee entity might have a unary
relationship called manages, where one
employee manages another employee.
o Entity: Employee
o Relationship: Manages (between
an employee and another Employee Manages
employee)

Binary Relationship: A relationship among 2 entity sets. Example: A professor teaches a course
and a courseis taught by a professor.

Ternary Relationship: A relationship among 3 entity sets. Example: A professor teaches a course in so
and sosemester.

2. Cardinality: Cardinality defines the number of instances of one entity that can or must be associated
with instances of another entity in a relationship.
The main types of cardinality are:
 One-to-One (1:1): Each instance of one entity is related to exactly one instance of another entity.
For example, a student has only
one identification card and an identification
card is given to one person.
 One-to-Many (1:M): An instance of one entity can be related to multiple instances of
another entity. For example, a customer
can place many orders, but an order
cannot be placed by many customers.

 Many-to-One (M:1): Many instances of one entity can be related to one instance of another entity.
 For example, many students can involve only on one project.
For example, students have to opt for a single
course, but a course can have many students.
 Many-to-Many (M:M): Instances of one entity can be related to many instances of another entity,
and vice versa. For example, students can enroll in multiple courses, and courses can have multiple
students.
 For example, you can assign an employee to
many projects and a project can have many
employees.

1.16. Constraints: In Database Management Systems (DBMS), constraints are rules enforced on
the data in tables to ensure accuracy, integrity, and consistency. They define certain conditions that data
must meet when being inserted, updated, or deleted from a database. Constraints play a critical role in
maintaining data integrity across various operations in a database. Types of Constraints: There are various
types of constraints in DBMS. They are
1. Domain Integrity Constraints
2. Entity Integrity Constraints
3. Referential Integrity Constraints
4. Key Constraints
1. Domain Integrity Constraints: Domain constraints in DBMS are the set of rules which defines
what kind of data can be stored in an attribute. Domain Constraints help us to enter the data into
the table according to the particular data type. The domain constraints are
a) NOT NULL 2) CHECK.
a) NOT NULL: It is domain constraint that a column cannot have a NULL value.
Syntax: CREATE TABLE <table name> (Column1 data type NOT NULL,
Column2 data type NOT NULL,
...
Column n data type NOT NULL); Example: CREATE
TABLE customers ( ID number(6) NOT NULL,
NAME varchar2(20), NOT NULL,
AGE number(3) NOT NULL, ADDRESS
char(25),
SALARY number(10,2),
PRIMARY KEY (ID));
b) CHECK: Check is a domain constraint, it allows the data into the table only after checking the
condition.
Syntax: CREATE TABLE Table_Name(column_name data_type(size) CHECK(logical expression), ….);
Example: CREATE TABLE student(Rollno number(6) NOT NULL, Name
varchar2(20) NOT NULL, Age number(3) CHECK (Age>=18));
2. Entity Integrity Constraint: Entity integrity ensures that every table has a primary key, and that the
primary key values are unique and non-null. This ensures that every entity (record) in a table can be
uniquely identified.
Example: A table of students must have a not-null, unique StudentID for each record.
create table student(StudentID number(5) NOT NULL, name varchar2(20), marks number(3),
grade char(1), primary key(StudentID));
3. Referential Integrity Constraint: Referential integrity (Foreign Key Constraints) is maintained
using foreign key constraints, which enforce a link between two tables. A foreign key in one table
refers to a primary key in another table. This constraint ensures that records in a table cannot
reference non-existent records in another table, preventing invalid data entries.
Example: Table-1 Department and Table-2 Employee
 CREATE TABLE Department (DeptID NUMBER(6) PRIMARY KEY, DeptName VARCHAR2(50) );

CREATE TABLE Employee (EmployeeID NUMBER(6) PRIMARY KEY, Name VARCHAR2(50), DeptID
NUMBER(2), FOREIGN KEY (DeptID) REFERENCES

Department(DeptID) );
4. Key Constraints: Key constraints in Database Management Systems (DBMS) ensure that data
within a table is uniquely identifiable and that the integrity of the data is maintained. Key constraints
are rules that are applied to keys, which are attributes (columns) used to identify rows (records) in a
table.
There are several types of key constraints in DBMS, and each has a specific role in maintaining data
integrity and preventing redundancy. The most common key constraints are Primary Key, Unique
Key, Foreign Key, and Composite Key.
a. Primary Key: A primary key is used in table that uniquely identifies each row/record. This
is also one type of Integrity Constraint. Primary keys must have distinct values. Null values
are not allowed in a primary key column. A table can
only have one primary key, which can be made up of one or more fields. It creates a
composite key when several fields are used as a primary key.
Syntax: Create table table_name (Column_name1 datatype NOT NULL, Column_name2 datatype,
..... Column_name n datatype, PRIMARY KEY (column_name1) ) .

Example: create table student(id number(6) not null, name varchar2(20), marks number(3),
grade varchar2(5), primary key(id));
b. Foreign key: A foreign key is used to define a column or group of columns in a relational
database table that provides a link between data in two tables. It acts as a cross-reference
between tables because it references the primary key of another table, thereby establishing a
link between them. It means, the foreign key constraint ensures that values in the foreign key
column(s) match values in the referenced primary key column(s).
Syntax:CREATE TABLE Table_Name ( col_name type(size) FOREIGN KEY(col_name)
REFERENCES table_name);

Example: Providing relation between Customer table and Order table.

CREATE TABLE customers (customerID number(6) PRIMARY KEY, FirstName
VARCHAR2(30), LastName VARCHAR2(30), Email VARCHAR2(50) UIQUE);
CREATE TABLE orders (orderID NUMBER(6) PRIMARY KEY, customerID
NUMBER(6), orderdate DATE, TotalAmount NUMBER(10, 2), FOREIGN KEY
(customerID REFERENCES customers(customerID));

customers table
CustomerID FirstName LastName Email

orders table

OrderID CustomerID orderdate TotalAmount

c. Unique Constraint:
 A unique constraint ensures that all values in a column or a set of columns are unique.
Unlike the primary key, a unique constraint allows NULL values.
Syntax
CREATE TABLE Table_name (
 Column_Name1 DataType NOT NULL UNIQUE,
Column_Name2 DataType NOT NULL, Column_Name3
DataType,
.......
Column_Name N DataType);
Example:
CREATE TABLE Student_DB (S_ID number NOT NULL UNIQUE, L_Name
varchar2(255) NOT NULL, F_Name varchar2(255), Age number);

1.17. Sub Class and Super Class:

Sub Class: In a database management system (DBMS), a subclass
is a type of entity that is a subgroup of another entity type.
Subclasses share common attributes or relationships that are
distinct from other subgroups.
For example, if subclass P inherits from superclass Q, and subclass
Q inherits from superclass S, then an instance of P is an instance
of Q and also (by transitivity) an instance of S.

Super Class: Super classes are components of data

inheritance where certain properties, attributes, or
characteristics of an entity are inherited or passed
down from a parent object to its child objects or
entities.

Car, Truck and Motorcycle are all subclasses of the

superclass Vehicle.

1.18. Inheritance: It refers to the ability of a subclass (or specialized entity) to inherit attributes and
relationships from a superclass (or general entity). This allows for more efficient and logical modeling of
hierarchical relationships within a database.
Superclass and Subclass:
 A superclass is a general entity that holds attributes and relationships common to multiple
entities.
 A subclass is a specialized entity that inherits all the attributes and relationships of the
superclass but may have additional attributes or relationships specific to itself.
Attributes Inheritance:
 A subclass inherits all the attributes of the superclass, meaning the subclass automatically
includes the attributes of the superclass without redefining them.
Relationships Inheritance:
  A subclass also inherits the relationships that the superclass participates in. For example, if the
superclass has a relationship with another entity, the subclass will also have the same relationship.
Types of Inheritance in DBMS: 1. Generalization and 2. Specialization.
1. Generalization: Generalization is a bottom-up approach in which two lower level entities
combine to form a higher-level entity. In generalization, the higher-level entity can also
combine with other lower level entity to make further higher-level entity.
For example, if both Car and Truck share common attributes like VehicleID, Make, and Model,
these attributes can be abstracted into a Vehicle superclass, from which both Car and Truck will
inherit.

Specialization: Specialization is the opposite of generalization. It is the process of creating a more specific
entity (subclass) from a more general one (superclass). The specialized subclass inherits the common attributes
and relationships of the superclass but can also have its own unique attributes. For example, the entity Person
can be specialized into Student and Teacher. In the diagram, the higher-level entity called Product, which
represents all products. Then, there are specialized entities for specific product categories, such as Electronics,
Clothing, and Books, with each having their own specialized attributes.
UNIT -2
 DBMS
(R23 Syllabus)
UNIT II:
Relational Model: Introduction to relational model, concepts of domain, attribute, tuple, relation,
importance of null values, constraints (Domain, Key constraints, integrity constraints) and their
importance, Relational Algebra, Relational Calculus. BASIC SQL: Simple Database schema, data
types, table definitions (create, alter), different DML operations (insert, delete, update).

2.1. Introduction to relational model: In 1970, E.F. Codd proposed the relational Model
to model data in the form of relations or tables. After designing the conceptual model of the
Database using ER diagram, we need to convert the conceptual model into a relational model
which can be implemented using any RDBMS language like Oracle SQL, MySQL, etc. So, we
will see what the Relational Model is.
Key Concepts of the Relational Model:
1. Relation (Table):
o A relation is a table with rows and columns. Each relation represents a specific entity or
concept.
o The rows (also called tuples) represent individual records.
o The columns represent attributes of the entity.
2. Tuple (Row):
o A tuple is a single row in a table, representing a single record with specific attribute values.
3. Attribute (Column):
o An attribute is a named column of a relation. It holds values from a specific domain (e.g., an
attribute "Age" may hold integers).
4. Domain:
o A domain is the set of possible values that an attribute can take. For instance, a "Gender"
attribute might have a domain containing the values {Male, Female, Other}.
5. Schema:
o The schema defines the structure of a relation. It specifies the relation name, attributes, and
their types.
o Example: Student (StudentID, Name, Age, Gender)
6. Primary Key:
o A primary key is a unique identifier for each tuple in a relation. No two rows in a table can
have the same value for the primary key attribute(s).
o Example: In a "Student" table, "StudentID" might be the primary key.
7. Foreign Key:
o A foreign key is an attribute (or a set of attributes) in one table that refers to the primary key
in another table. This is used to establish relationships between tables.
8. Relation Operations: The relational model supports a set of operations (called relational
algebra) for manipulating relations. Some of the core operations are:
o Selection: Extracts rows from a table that meet a specific condition.
o Projection: Extracts specific columns from a table.
o Join: Combines related rows from two or more tables.
o Union, Intersection, and Difference: Used for set operations on relations.
Advantages of the Relational Model:
1. Simplicity: Data is organized into simple tables, which are easy to understand and manipulate.
2. Data Integrity: Constraints (like primary and foreign keys) ensure data consistency and integrity.
3. Flexibility: Tables can be easily joined or modified, and complex queries can be written to retrieve
data.
4. Normalization: The relational model supports techniques like normalization to reduce redundancy
and improve efficiency.
Example: Consider two tables, Students and Courses.
StudentID Name Age Gender
101 Alice 20 Female
102 Bob 22 Male
103 Carol 19 Female
CourseID CourseName StudentID
C01 Mathematics 101
C02 Physics 102
C03 Chemistry 103
 Primary Key: "StudentID" in the "Students" table.
 Foreign Key: "StudentID" in the "Courses" table references the "Students" table.
In this case, the Students table represents the main data about students, while the Courses table
shows the courses each student is enrolled in, linking back to the primary key in the Students table.
The relational model provides a structured and highly efficient way of managing large amounts of
interrelated data in modern databases.
2.2. Concepts of domain
In a Database Management System (DBMS), a domain refers to the set of permissible values that
an attribute (column) can take. It is essentially a constraint on the attribute, defining the kind of
data that can be stored in that column. Domains are crucial because they help ensure data integrity
by restricting the values that can be entered into a database.
Key Aspects of a Domain:
1. Data Type: The domain defines the data type of the attribute, such as integers, floating-
point numbers, strings, dates, or Boolean values. For example:
 An attribute "Age" might have the domain of integers.
 An attribute "Date of Birth" might have the domain of dates.
2. Range or Set of Values:
o Domains can specify a range or specific set of values that an attribute can take. For
example:
 The "Gender" attribute might have the domain of {Male, Female,
Other}.
 The "Age" attribute might have a domain specifying valid ages between 0
and 120.
3. Null Values:
o The domain may allow or disallow NULL values, depending on whether the
attribute can contain missing or undefined data.
4. User-Defined Domains:
o In addition to built-in data types, some DBMSs allow users to define custom
domains with specific constraints, such as creating a domain for "Country Codes"
where only valid country abbreviations are allowed.
Example of Domain: Consider a table Students with the following schema:
Attribute Data Type Domain
StudentID Integer Positive integers
Name String Non-empty string
Age Integer Values between 0 and 120
Gender String {Male, Female, Other}
DateOfBirth Date Valid date values
In this example:
 The "StudentID" domain restricts the values to positive integers.
 The "Age" attribute has a domain that limits values between 0 and 120.
 The "Gender" attribute can only have values from the set {Male, Female, Other}.
Importance of Domains:
1. Data Integrity: By restricting attributes to specific domains, the database ensures that only
valid and meaningful data can be stored, preventing errors.
 2. Consistency: Domains enforce consistency in the data across the database, especially when
multiple tables use the same domain (e.g., different tables storing gender data will always
use the same set of valid values).
3. Validation: Domains help validate data at the time of entry, ensuring that any invalid or
inappropriate values are rejected.
Domain Constraints:
 Domains can have additional constraints, such as uniqueness, non-null constraints, or even
foreign key references to enforce referential integrity.
In short, a domain in DBMS is a critical concept that defines the valid set of values for each attribute,
ensuring that the data stored in the database is accurate, consistent, and follows a predefined
structure.
2.3. Attribute:
In a Database Management System (DBMS), an attribute refers to a column in a table, representing
a specific property or characteristic of the entity that the table models. Each attribute contains data
of a particular type, and its values describe or define various aspects of the records (or rows) within
the table.
Key Points about Attributes:
1. Columns in a Table:
o An attribute is equivalent to a column in a relational database table.
o Each table represents an entity (e.g., Student, Employee), and each attribute
represents a specific detail or property of that entity.
2. Data Type:
o Each attribute has an associated data type, which defines the kind of data the
attribute can store, such as:
 Integer: Whole numbers (e.g., Age, EmployeeID)
 String: Text or characters (e.g., Name, Address)
 Date: Date values (e.g., DateOfBirth)
 Boolean: True/False values
3. Attribute Values:
o Each record (or tuple) in a table has a value for each attribute.
o The value for an attribute may be mandatory (required) or optional (null) based on
the design of the database and any constraints applied.
4. Domains:
o Each attribute belongs to a specific domain, which defines the set of permissible
values that the attribute can take. For example, an attribute like "Age" may have a
domain that only allows integers between 0 and 120.
5. Key Attributes:
o Primary Key: An attribute (or set of attributes) that uniquely identifies each tuple
(row) in the table. For example, "StudentID" in a "Students" table.
o Foreign Key: An attribute that references the primary key of another table, creating
relationships between tables. For example, "StudentID" in an "Enrollments" table,
which refers to the "Students" table.
6. Composite Attributes:
o A composite attribute consists of multiple components or sub-attributes. For
example, an attribute "FullName" might be split into "FirstName" and "LastName."
7. Derived Attributes:
o These are attributes that can be calculated from other attributes. For instance, "Age"
could be derived from the "DateOfBirth" attribute.

Example: Consider the Students table with the following attributes:

Attribute Data Type Description
 StudentID Integer Unique identifier for each student
Name String Name of the student
Age Integer Age of the student
Gender String Gender of the student (e.g., Male, Female, Other)
DateOfBirth Date Birthdate of the student
In this example:
 "StudentID" is an attribute that uniquely identifies each student (primary key).
 "Name", "Age", and "Gender" describe the student.
 "DateOfBirth" holds the student's date of birth.

2.4. Tuple: In DBMS (Database Management System), a tuple refers to a single row or record
in a table. A tuple represents a set of values corresponding to the attributes (or columns) of a table,
where each attribute has a specific data type. Essentially, a tuple is a collection of related data points
that describe a specific entity or object.
Example: Consider a table named Students:
Student_ID Name Age Grade
101 Alice 20 A
102 Bob 21 B
103 Charlie 22 A
Each row in this table (e.g., (101, Alice, 20, A)) is a tuple. In this context:
 The table represents a relation.
 Each column represents an attribute.
 Each row (or tuple) represents an instance of the relation.

2.5. Relation: In DBMS (Database Management System), a relation refers to a table that stores
data in a structured format. A relation is a collection of tuples (rows) that share a common set of
attributes (columns). Each relation represents an entity or a concept, and its structure is defined by
its attributes.
Key Components of a Relation:
1. Attributes (Columns): These are the characteristics or properties of the entity represented
by the relation. Each attribute has a data type and defines what kind of data can be stored.
2. Tuples (Rows): A tuple is a single record in the relation, representing one instance of the
entity. Each tuple contains a set of attribute values.
3. Domain: The domain refers to the possible values that an attribute can take. For instance, if
an attribute is "Age", its domain might be restricted to positive integers.
4. Degree: The degree of a relation is the number of attributes or columns it has.
5. Cardinality: The cardinality of a relation is the number of tuples or rows it contains.
Example: Consider a Students relation:
Student_ID Name Age Grade
101 Alice 20 A
102 Bob 21 B
103 Charlie 22 A
 Relation: The table "Students" is the relation.
 Attributes: Student_ID, Name, Age, Grade.
 Tuples: Each row like (101, Alice, 20, A) is a tuple.
2.6. Importance of null values:
In DBMS (Database Management System), the use of NULL values is important for representing
missing, unknown, or inapplicable data. Here are the key uses of NULL values in a database:
 1. For example, if the birthdate of a customer is not available, it can be stored as NULL.
2. For instance, in an employee database, the "Passport Number" field can be NULL
for employees who do not have passports.
3. For example, in a user registration form, fields like "Alternate Phone Number" or
"Middle Name" might be optional and can be left as NULL if not filled out.
4. NULL allows you to clearly express that the data is missing, rather than incorrect.
5. For example, if an employee is not assigned to any project, the foreign key for the
project ID in the employee table can be NULL.
6. NULL helps maintain the flexibility and integrity of the database by allowing
incomplete records to be stored without introducing incorrect or default data.
7. NULL can be used in SQL queries to search for records where specific fields have
missing data. Functions like IS NULL or IS NOT NULL allow users to filter or identify
records with missing information.
Example: SELECT * FROM Employees WHERE Department_ID IS NULL;
8.
9. When performing operations on Aggregate functions like SUM(), AVG(), COUNT(),
etc., NULL values are typically ignored. This behavior allows the database to avoid
incorrect calculations when data is missing.
For example: SELECT AVG(Salary) FROM Employees;
 In this query, employees with a NULL salary are excluded from the average
calculation.
Syntax: CREATE TABLE <table name> (Column1 data type NOT NULL,
Column2 data type NOT NULL,
...
Column n data type NOT NULL);
Example: CREATE TABLE customers( ID number(6) NOT NULL,
NAME varchar2(20), NOT NULL,
AGE number(3) NOT NULL,
ADDRESS char(25),
SALARY number(10,2),
PRIMARY KEY (ID));
2.7. Constraints and their importance: In DBMS (Database Management System),
constraints are rules or restrictions applied to the data in a table to ensure accuracy, consistency,
and integrity. These constraints help to maintain the quality of the data by restricting the types of
values that can be inserted into a table.
Types of Constraints: There are various types of constraints in DBMS. They are
1. Domain Integrity Constraints
2. Entity Integrity Constraints
3. Referential Integrity Constraints
4. Key Constraints
1. Domain Integrity Constraints: Domain constraints in DBMS are the set of rules which
defines what kind of data can be stored in an attribute. Domain Constraints help us to enter
the data into the table according to the particular data type. The domain constraints are
a) NOT NULL 2) CHECK.

a) NOT NULL: It is domain constraint that a column cannot have a NULL value.
Syntax: CREATE TABLE <table name> (Column1 data type NOT NULL,
Column2 data type NOT NULL,
...
Column n data type NOT NULL);
 Example: CREATE TABLE customers ( ID number(6) NOT NULL,
NAME varchar2(20), NOT NULL,
AGE number(3) NOT NULL,
ADDRESS char(25),
SALARY number(10,2),
PRIMARY KEY (ID));

b) CHECK: Check is a domain constraint, it allows the data into the table only after checking
the condition.
Syntax: CREATE TABLE Table_Name(column_name data_type(size) CHECK(logical
expression), ….);
Example: CREATE TABLE student(Rollno number(6) NOT NULL, Name
varchar2(20) NOT NULL, Age number(3) CHECK (Age>=18));
2. Entity Integrity Constraint: Entity integrity ensures that every table has a primary key, and
that the primary key values are unique and non-null. This ensures that every entity (record)
in a table can be uniquely identified.
Example: A table of students must have a not-null, unique StudentID for each record.
create table student(StudentID number(5) NOT NULL, name varchar2(20), marks
number(3), grade char(1), primary key(StudentID));
3. Referential Integrity Constraint: Referential integrity (Foreign Key Constraints) is
maintained using foreign key constraints, which enforce a link between two tables. A foreign
key in one table refers to a primary key in another table. This constraint ensures that records
in a table cannot reference non-existent records in another table, preventing invalid data
entries.
Example: Table-1 Department and Table-2 Employee
CREATE TABLE Department (DeptID NUMBER(6) PRIMARY KEY, DeptName
VARCHAR2(50) );
CREATE TABLE Employee (EmployeeID NUMBER(6) PRIMARY KEY, Name
VARCHAR2(50), DeptID NUMBER(2), FOREIGN KEY (DeptID) REFERENCES
Department(DeptID) );
4. Key Constraints: Key constraints in Database Management Systems (DBMS) ensure
that data within a table is uniquely identifiable and that the integrity of the data is maintained.
Key constraints are rules that are applied to keys, which are attributes (columns) used to
identify rows (records) in a table.
There are several types of key constraints in DBMS, and each has a specific role in
maintaining data integrity and preventing redundancy. The most common key constraints
are Primary Key, Unique Key, Foreign Key, and Composite Key.
a. Primary Key: A primary key is used in table that uniquely identifies each
row/record. This is also one type of Integrity Constraint. Primary keys must have
distinct values. Null values are not allowed in a primary key column. A table can
only have one primary key, which can be made up of one or more fields. It creates a
composite key when several fields are used as a primary key.
Syntax: Create table table_name (Column_name1 datatype NOT NULL, Column_name2
datatype, ..... Column_name n datatype, PRIMARY KEY (column_name1) ) .
Example: create table student(id number(6) not null, name varchar2(20), marks
number(3), grade varchar2(5), primary key(id));
b. Foreign key: A foreign key is used to define a column or group of columns in a
relational database table that provides a link between data in two tables. It acts as a
 cross-reference between tables because it references the primary key of another table,
thereby establishing a link between them. It means, the foreign key constraint ensures
that values in the foreign key column(s) match values in the referenced primary key
column(s).
Syntax:CREATE TABLE Table_Name ( col_name type(size) FOREIGN
KEY(col_name) REFERENCES table_name);
Example: Providing relation between Customer table and Order table.
CREATE TABLE customers (customerID number(6) PRIMARY KEY, FirstName
VARCHAR2(30), LastName VARCHAR2(30), Email VARCHAR2(50) UIQUE);
CREATE TABLE orders (orderID NUMBER(6) PRIMARY KEY, customerID
NUMBER(6), orderdate DATE, TotalAmount NUMBER(10, 2), FOREIGN KEY
(customerID REFERENCES customers(customerID));
customers table
CustomerID FirstName LastName Email

orders table
OrderID CustomerID orderdate TotalAmount

c. Unique Constraint:
 A unique constraint ensures that all values in a column or a set of columns are
unique. Unlike the primary key, a unique constraint allows NULL values.
Syntax
CREATE TABLE Table_name (
Column_Name1 DataType NOT NULL UNIQUE,
Column_Name2 DataType NOT NULL,
Column_Name3 DataType,
.......
Column_Name N DataType);
Example: CREATE TABLE Student_DB (S_ID number NOT NULL UNIQUE, L_Name varchar2(255)
NOT NULL, F_Name varchar2(255), Age number);
2.8. Relational Algebra: Relational Algebra is a procedural query language used in Database
Management Systems (DBMS) to manipulate and query relational data. It forms the theoretical
foundation of SQL and is used to retrieve data from relations (tables). In relational algebra,
operations are performed on one or more relations to produce a new relation.
Basic Operations in Relational Algebra
1. Selection (σ) (sigma):
 The selection operation selects rows (tuples) from a relation that satisfy a given
condition.
 Syntax: σ condition (Relation)
Example-1: SQL QUERY: SELECT * FROM STUDENT;
ALGEBRA QUERY: σ (STUDENT);
Example-2: SQL-QUERY: SELECT * FROM STUDENT WHERE GRADE =
‘A’;
ALGEBRA-QUERY : σ grade = ‘A’ (student)
It will display all rows from the ‘student’ table where the grade = ‘A’.


Example-1: Student table [INPUT]

SNO NAME COURSE GRADE
 1 BASKAR CSE A
2 RAJESH CSE B
3 KALYAN CSE A
4 KAVYA CSE C
5 SUPRAJA CSE A
6 VAASU CSE B
OUTPUT:
SNO NAME COURSE GRADE
1 BASKAR CSE A
3 KALYAN CSE A
4 SUPRAJA CSE A

1. Projection (π):
o The projection operation selects specific columns (attributes) from a relation,
removing duplicates.
o Notation: π_{attribute1, attribute2, ..., attributeN}(R)
o Example: π Name, Age (Person) selects only the Name and Age columns from the
Person table.
2. Union (∪):
o The union operation combines tuples from two relations, removing duplicates (both
relations must have the same set of attributes).
o Notation: R1 ∪ R2
o Example: Student ∪ Teacher gives a relation containing all tuples present in either
the Student or Teacher relation.
3. Set Difference (-):
o The set difference operation finds tuples that are in one relation but not in the other.
o Notation: R1 - R2
o Example: Employee - Manager gives tuples that are in the Employee relation but not
in the Manager relation.
4. Cartesian Product (×):
o The Cartesian product operation combines every tuple of one relation with every
tuple of another relation, producing all possible combinations.
o Notation: R1 × R2
o Example: Student × Teacher returns all possible combinations of tuples from Student
and Teacher.
5. Rename (ρ):
o The rename operation allows you to rename a relation or its attributes.
o Notation: ρ_{new_relation_name}(R) or ρ_{new_attr1,
new_attr2}(R)
o Example: ρ S(Student) renames the Student relation to S.
Additional Operations
1. Intersection (∩):
o The intersection operation retrieves tuples that are present in both relations.
o Notation: R1 ∩ R2
o Example: Student ∩ Teacher gives a relation containing tuples present in both
Student and Teacher relations.
Example Query Using Relational Algebra
1. Find the names of all employees who work in the ‘HR’ department:
π Name (σ Dept = 'HR' (Employee))
2. Find all departments that have no employees:
π Dept (Department) - π Dept (Employee)
2.9. Relational Calculus: Relational Calculus is a non-procedural query language used in
Database Management Systems (DBMS), focusing on what to retrieve rather than how to retrieve
it. It is different from relational algebra, which is a procedural language. In relational calculus, you
define the properties of the data you want without specifying a step-by-step procedure to obtain it.
There are two types of relational calculus:
1. Tuple Relational Calculus (TRC):
 In tuple relational calculus, queries are expressed in terms of tuples. It uses tuple variables that
range over a relation, and conditions are specified on the attributes of the tuple.
 A TRC query has the form:
{ t | P(t) }
 t is a tuple variable.
 P(t) is a condition or formula in the predicate logic.
 The result of the query includes all the tuples t that satisfy the condition P(t).
Example: This retrieves all tuples t from the Students relation where the age attribute is greater than 18.
2. Domain Relational Calculus (DRC):
 In domain relational calculus, queries are expressed using domain variables that take values
from the attributes of the relations, rather than the tuples themselves.
 A DRC query has the form:
{ <x1, x2, ..., xn> | P(x1, x2, ..., xn) }
o x1, x2, ..., xn are domain variables.
o P(x1, x2, ..., xn) is a condition or formula in the predicate logic.

Example: { <x, y> | ∃ z (Students(x, y, z) AND z > 18) }

This retrieves pairs of student names and IDs (<x, y>) where there exists an age z in the Students
relation that is greater than 18.
Key Characteristics:
 Declarative: Both TRC and DRC are declarative, meaning they specify what to retrieve rather than
how to retrieve it.
 Safe Expressions: A query is considered safe if it returns a finite set of results. Unsafe queries may
produce infinite sets.

2.10. BASIC SQL: Simple Database schema, data types, table definitions (create,
alter), different DML operations (insert, delete, update).
2.10.1. DDL: Data Definition Language (DDL) statements are used to define the database
structure orschema.
DDL Commands: Create, Alter, Drop, Rename, TruncateCREATE - to create objects in the database
ALTER - alters the structure of the databaseDROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are
removedRENAME - rename an object
1. The CREATE TABLE statement is used to create a new table in a database.
Syntax-1
CREATE TABLE <table name> (column name1 data type1, column name2 data type2, … column
name n data type n);
Example: CREATE TABLE date_1(dd number(2), mm number(2), year number(4));
Table is created.
Syntax-2
CREATE TABLE <table name> (column name1 data type1 constrain, column name2 data type2,
… column name n data type n);
Example: CREATE TABLE Student( rollno varchar2(10) primary key, name varchar2(10), dob
date, course varchar2(20), course_fees number(10,2), street varchar2(10), city varchar2(10), state
varchar2(10), pincode number(6));
Table is created.
Syntax-3
CREATE TABLE <table name> AS SELECT column1, column2, - - - FROM <existing table
name> WHERE <condition>
This command is used to new_table with existing table data.
Example: CREATE TABLE library_table AS SELECT rollno, ename, course FROM student
WHERE course = “CSE-DS”;
1. ALTER Command: Alter command is used to add a new column, rename the column, change
the size of column or drop the column from given table.
a. ADD command: This command is used to add new column. It is used in ALTER command.
Syntax: ALTER TABLE <table name> ADD <column name> <data type>;
Example: ALTER TABLE student ADD email varchar2(35);
b. RENAME command: This command is used to rename the column. It is using in ALTER
command.
Syntax: ALTER TABLE <table name> RENAME COLUMN old name TO new name;
Example: ALTER TABLE student RENAME COLUMN name TO student_name;
c. ALTER Command: This is used to change the column size. It is used in ALTER command.
Syntax: ALTER TABLE <table name> ALTER COLUMN <column name> <data type>;
Example: ALTER TABLE student ALTER name varchar2(25);
d. DROP Command: This is used to drop the column from table. It is used in ALTER command.
Syntax: ALTER TABLE <table name> DROP <column name>;
Example: ALTER TABLE student DROP email;
e. DROP/TRUNCATE: This command is used to drop or remove the table from sql.
Syntax: DROP TABLE <table name>; (or) Syntax: TRUNCATE TABLE <table name>;
Example: DROP TABLE student; (or) TRUNCATE TABLE student;

2.10.2. DQL (Data Query Language)

The DQL command is used to get the data from the database. It is done by SELECT
command/statement.
Syntax-1: SELECT <column1>, <column2>,......... FROM <table name>;
Syntax-2: SELECT * FROM <table name>;
Syntax-3: SELECT <column1>, <column2>,......... FROM <table name> WHERE clause;
Note: WHERE clause has various options.

Example-1: SELECT rollno, name, course FROM student;

Example-2: SELECT * FROM student;
Example-3: SELECT rollno, name, course FROM student WHERE course_name = “CSE-DS”;

2.10.3. DML commands:

The DML commands are used to manipulate the table. The DML Command are INSERT, UPDATE
and DELETE.

INSERT command: This command is used to insert data into Tables. After creation of table, the
insert command is used to insert the records (data) into table.
Syntax:
a. INSERT INTO <table name> (<&col1>,<&col2>) VALUES(<exp>,<exp>); (or)
b. INSERT INTO <table name> VALUES (<exp/data>,<exp/data>);
Example-1: INSERT INTO student (&rollno, ‘&name’,’&course’) VALUES (32101, VASU,
CSE);
Example-2: INSERT INTO student VALUES (32101, VASU, CSE);
Example-3: INSERT INTO student (&rollno, ‘&name’,’&course’);

UPDATE command: This command is used to update or modify the existing records or data in the
table
Syntax-1: Update <tablename> set <col1>=<exp1>, <col2>=<exp2> . . . ;
Syntax-2: Update <tablename> set <col1>=<exp1>, <col2>=<exp2> . . . where <condition>;
Example: UPDATE student SET name = 'sreenivas' WHERE rollno = 32101;

DELETE command: This command is used to delete or remove the existing records or data from
the table
Syntax: DELETE FROM <tablename>;
Syntax: DELETE FROM <tablename> WHERE <condition>;
Example-1: DELETE FROM student;
Example-2: DELETE FROM student WHERE rollno = 32105;