MAHARANA PRATAP GROUP OF INSTITUTIONS

KOTHI MANDHANA, KANPUR

(Approved by AICTE, New Delhi and Affiliated to Dr.AKTU, Lucknow)

Digital Notes
[Department ofComputer Application]
Subject Name : Database Management System

Subject Code : KCA 204

Course : MCA
Branch :
Semester : 2nd
Prepared by : Avinash Bajpai

Reference No./CA/Avinash Bajpai/KCA 204/1/2

 Unit 2

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
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.

Tpes 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.
Notation of ER diagram

Database can be represented using the notations. In ER diagram, many notations

are used to express the cardinality. These notations are as follows:

Mapping Constraints

o 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.

Keys

o Keys play an important role in the relational database.

o It is used to uniquely identify any record or row of data from the table. It is
also used to establish and identify relationships between tables.

For example: In Student table, ID is used as a key because it is unique for each
student. In PERSON table, passport_number, license_number, SSN are keys since
they are unique for each person.

Types of key:

1. Primary key

o It is the first key which is used to identify one and only one instance of an
entity uniquely. An entity can contain multiple keys as we saw in PERSON
table. The key which is most suitable from those lists become a primary key.
o In the EMPLOYEE table, ID can be primary key since it is unique for each
employee. In the EMPLOYEE table, we can even select License_Number
and Passport_Number as primary key since they are also unique.
 o For each entity, selection of the primary key is based on requirement and
developers.

2. Candidate key

o A candidate key is an attribute or set of an attribute which can uniquely

identify a tuple.
o The remaining attributes except for primary key are considered as a
candidate key. The candidate keys are as strong as the primary key.

For example: In the EMPLOYEE table, id is best suited for the primary key. Rest
of the attributes like SSN, Passport_Number, and License_Number, etc. are
considered as a candidate key.

1. Difference between Primary and Candidate Key:

S.N
O Primary Key Candidate Key

Primary key is a minimal super key. So

While in a relation there can be
1. there is one and only one primary key in
more than one candidate key.
a relation.

While in Candidate key any

Any attribute of Primary key can not
2. attribute can contain NULL
contain NULL value.
value.

But without candidate key

Primary key can be optional to specify
3. there can’t be specified any
any relation.
relation.

Candidate specifies the key

Primary key specifies the important
4. which can qualify for primary
attribute for the relation.
key.

5. Its confirmed that a primary key is a But Its not confirmed that a
candidate key. candidate key can be a primary
S.N
O Primary Key Candidate Key

key.

Differences between Primary Key and Unique Key

Parameters PRIMARY KEY UNIQUE KEY

Used to serve as a unique Uniquely determines a

Basic identifier for each row in a row that isn’t the
table. primary key.

Cannot accept NULL Can accept only one

NULL value acceptance
values. NULL values.

Number of keys that

More than one unique
can be defined in the Only one primary key
key
table

Creates non-clustered
Index Creates clustered index
index

A unique key does not

A Primary key supports
Auto Increment support auto-increment
auto-increment value.
value.

We cannot change or
We can change unique
Modification delete values stored in
key values.
primary keys.
3. Super Key

Super key is a set of an attribute which can uniquely identify a tuple. Super key is a
superset of a candidate key.

For example: In the above EMPLOYEE table, for(EMPLOEE_ID,

EMPLOYEE_NAME) the name of two employees can be the same, but their
EMPLYEE_ID can't be the same. Hence, this combination can also be a key.

The super key would be EMPLOYEE-ID, (EMPLOYEE_ID, EMPLOYEE-

NAME), etc.

4. Foreign key

o Foreign keys are the column of the table which is used to point to the
primary key of another table.
o In a company, every employee works in a specific department, and
employee and department are two different entities. So we can't store the
information of the department in the employee table. That's why we link
these two tables through the primary key of one table.
o We add the primary key of the DEPARTMENT table, Department_Id as a
new attribute in the EMPLOYEE table.
o Now in the EMPLOYEE table, Department_Id is the foreign key, and both
the tables are related.

Generalization

o Generalization is like a bottom-up approach in which two or more entities of

lower level combine to form a higher level entity if they have some
attributes in common.
o In generalization, an entity of a higher level can also combine with the
entities of the lower level to form a further higher level entity.
o Generalization is more like subclass and superclass system, but the only
difference is the approach. Generalization uses the bottom-up approach.
o In generalization, entities are combined to form a more generalized entity,
i.e., subclasses are combined to make a superclass.

For example, Faculty and Student entities can be generalized and create a higher
level entity Person.
Specialization

o Specialization is a top-down approach, and it is opposite to Generalization.

In specialization, one higher level entity can be broken down into two lower
level entities.
o Specialization is used to identify the subset of an entity set that shares some
distinguishing characteristics.
o Normally, the superclass is defined first, the subclass and its related
attributes are defined next, and relationship set are then added.

For example: In an Employee management system, EMPLOYEE entity can be

specialized as TESTER or DEVELOPER based on what role they play in the
company.
Aggregation

In aggregation, the relation between two entities is treated as a single entity. In

aggregation, relationship with its corresponding entities is aggregated into a higher
level entity.

For example: Center entity offers the Course entity act as a single entity in the
relationship which is in a relationship with another entity visitor. In the real world,
if a visitor visits a coaching center then he will never enquiry about the Course
only or just about the Center instead he will ask the enquiry about both.
Reduction of ER diagram to Table

The database can be represented using the notations, and these notations can be
reduced to a collection of tables.

In the database, every entity set or relationship set can be represented in tabular
form.

The ER diagram is given below:

There are some points for converting the ER diagram to the table:

o Entity type becomes a table.

In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms

individual tables.

o All single-valued attribute becomes a column for the table.

In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column

of STUDENT table. Similarly, COURSE_NAME and COURSE_ID form the
column of COURSE table and so on.

o A key attribute of the entity type represented by the primary key.

In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and

LECTURE_ID are the key attribute of the entity.

o The multivalued attribute is represented by a separate table.

In the student table, a hobby is a multivalued attribute. So it is not possible to
represent multiple values in a single column of STUDENT table. Hence we create
a table STUD_HOBBY with column name STUDENT_ID and HOBBY. Using
both the column, we create a composite key.

o Composite attribute represented by components.

In the given ER diagram, student address is a composite attribute. It contains

CITY, PIN, DOOR#, STREET, and STATE. In the STUDENT table, these
attributes can merge as an individual column.

o Derived attributes are not considered in the table.

In the STUDENT table, Age is the derived attribute. It can be calculated at any
point of time by calculating the difference between current date and Date of Birth.

Using these rules, you can convert the ER diagram to tables and columns and
assign the mapping between the tables. Table structure for the given ER diagram is
as below:
Relationship of higher degree

The degree of relationship can be defined as the number of occurrences in one

entity that is associated with the number of occurrences in another entity.

There is the three degree of relationship:

1. One-to-one (1:1)
2. One-to-many (1:M)
3. Many-to-many (M:N)

1. One-to-one

o In a one-to-one relationship, one occurrence of an entity relates to only one

occurrence in another entity.
o A one-to-one relationship rarely exists in practice.
o For example: if an employee is allocated a company car then that car can
only be driven by that employee.
o Therefore, employee and company car have a one-to-one relationship.

2. One-to-many

o In a one-to-many relationship, one occurrence in an entity relates to many

occurrences in another entity.
o For example: An employee works in one department, but a department has
many employees.
o Therefore, department and employee have a one-to-many relationship.

3. Many-to-many
o In a many-to-many relationship, many occurrences in an entity relate to
many occurrences in another entity.
o Same as a one-to-one relationship, the many-to-many relationship rarely
exists in practice.
o For example: At the same time, an employee can work on several projects,
and a project has a team of many employees.
o Therefore, employee and project have a many-to-many relationship.
Relational Model concept

o Relational model can represent as a table with columns and rows. Each row
is known as a tuple. Each table of the column has a name or attribute.

Domain: It contains a set of atomic values that an attribute can take.

Relational instance: In the relational database system, the relational instance is

represented by a finite set of tuples. Relation instances do not have duplicate
tuples.

Relational schema: A relational schema contains the name of the relation and
name of all columns or attributes.

Relational key: In the relational key, each row has one or more attributes. It can
identify the row in the relation uniquely.

Example: STUDENT Relation

Attribute: It contains the name of a column in a particular table. Each

attribute Ai must have a domain, dom(Ai)

NAME ROLL_NO PHONE_NO ADDRESS AGE

Ram 14795 7305758992 Noida 24

Shyam 12839 9026288936 Delhi 35

Laxma 33289 8583287182 Gurugram 20

n

Mahesh 27857 7086819134 Ghaziabad 27

Ganesh 17282 9028 Delhi 40

9i3988
o In the given table, NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE
are the attributes.
o The instance of schema STUDENT has 5 tuples.
o t3 = <Laxman, 33289, 8583287182, Gurugram, 20>
Properties of Relations

o Name of the relation is distinct from all other relations.

o Each relation cell contains exactly one atomic (single) value
o Each attribute contains a distinct name
o Attribute domain has no significance
o tuple has no duplicate value
o Order of tuple can have a different sequence
o Relational Algebra
o Relational algebra is a procedural query language. It gives a step by step
process to obtain the result of the query. It uses operators to perform queries.

Types of Relational operation

1. Select Operation:

o The select operation selects tuples that satisfy a given predicate.

o It is denoted by sigma (σ).

Notation: σ p(r)

Where:

σ is used for selection prediction

r is used for relation
p is used as a propositional logic formula which may use connectors like: AND
OR and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
For example: LOAN Relation

BRANCH_NAM LOAN_NO AMOUNT

E

Downtown L-17 1000

Redwood L-23 2000

Perryride L-15 1500

Downtown L-14 1500

Mianus L-13 500

Roundhill L-11 900

Perryride L-16 1300

Input:

σ BRANCH_NAME="perryride" (LOAN)

Output:

BRANCH_NAM LOAN_NO AMOUNT

E

Perryride L-15 1500

Perryride L-16 1300

 2. Project Operation:

o This operation shows the list of those attributes that we wish to appear in the
result. Rest of the attributes are eliminated from the table.
o It is denoted by ∏.

Notation: ∏ A1, A2, An (r)

Where

A1, A2, A3 is used as an attribute name of relation r.

Example: CUSTOMER RELATION

NAME STREE CITY

T

Jones Main Harrison

Smith North Rye

Hays Main Harrison

Curry North Rye

Johnson Alma Brooklyn

Brooks Senator Brooklyn

Input:

1. ∏ NAME, CITY (CUSTOMER)

Output:
 NAME CITY

Jones Harrison

Smith Rye

Hays Harrison

Curry Rye

Johnson Brooklyn

Brooks Brooklyn

3. Union Operation:

o Suppose there are two tuples R and S. The union operation contains all the

It eliminates the duplicate tuples. It is denoted by ∪.

tuples that are either in R or S or both in R & S.
o

Notation: R ∪ S

A union operation must hold the following condition:

o R and S must have the attribute of the same number.

o Duplicate tuples are eliminated automatically.

Example:

DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO
 Johnson A-101

Smith A-121

Mayes A-321

Turner A-176

Johnson A-273

Jones A-472

Lindsay A-284

BORROW RELATION

CUSTOMER_NAME LOAN_NO

Jones L-17

Smith L-23

Hayes L-15

Jackson L-14

Curry L-93

Smith L-11
 Williams L-17

Input:

∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Johnson

Smith

Hayes

Turner

Jones

Lindsay

Jackson

Curry

Williams

Mayes
4. Set Intersection:

o Suppose there are two tuples R and S. The set intersection operation
contains all tuples that are in both R & S.
o It is denoted by intersection ∩.

Notation: R ∩ S

Example: Using the above DEPOSITOR table and BORROW table

Input:

∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Smith

Jones

5. Set Difference:

o Suppose there are two tuples R and S. The set intersection operation
contains all tuples that are in R but not in S.
o It is denoted by intersection minus (-).

Notation: R - S

Example: Using the above DEPOSITOR table and BORROW table

Input:

∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)

Output:
 CUSTOMER_NAME

Jackson

Hayes

Willians

Curry

6. Cartesian product

o The Cartesian product is used to combine each row in one table with each
row in the other table. It is also known as a cross product.
o It is denoted by X.

Notation: E X D

Example:

EMPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A

2 Harry C

3 John B

DEPARTMENT
 DEPT_NO DEPT_NAME

A Marketing

B Sales

C Legal

Input:

EMPLOYEE X DEPARTMENT

Output:

EMP_ID EMP_NAME EMP_DEPT DEPT_N DEPT_NAME

O

1 Smith A A Marketing

1 Smith A B Sales

1 Smith A C Legal

2 Harry C A Marketing

2 Harry C B Sales

2 Harry C C Legal

3 John B A Marketing
 3 John B B Sales

3 John B C Legal

7. Rename Operation:

The rename operation is used to rename the output relation. It is denoted

by rho (ρ).

Example: We can use the rename operator to rename STUDENT relation to

STUDENT1.

ρ(STUDENT1, STUDENT)
Note: Apart from these common operations Relational algebra can be used in Join
operations.

Join Operations:

given join condition is satisfied. It is denoted by ⋈.

A Join operation combines related tuples from different relations, if and only if a

Example:

EMPLOYEE

EMP_CODE EMP_NAME

101 Stephan

102 Jack

103 Harry

SALARY
 EMP_CODE SALARY

101 50000

102 30000

103 25000

1. Operation: (EMPLOYEE ⋈ SALARY)

Result:

EMP_CODE EMP_NAME SALARY

101 Stephan 50000

102 Jack 30000

103 Harry 25000

 Types of Join operations:

1. Natural Join:

o A natural join is the set of tuples of all combinations in R and S that are

It is denoted by ⋈.
equal on their common attribute names.
o

Example: Let's use the above EMPLOYEE table and SALARY table:

Input:

1. ∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)

Output:

EMP_NAME SALARY
 Stephan 50000

Jack 30000

Harry 25000

SQL

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

2. Outer Join:

The outer join operation is an extension of the join operation. It is used to deal with
missing information.

Example:

EMPLOYEE

EMP_NAM STREET CITY

E

Ram Civil line Mumbai

Shyam Park street Kolkata

Ravi M.G. Street Delhi

Hari Nehru nagar Hyderabad

 FACT_WORKERS

EMP_NAM BRANCH SALARY

E

Ram Infosys 10000

Shyam Wipro 20000

Kuber HCL 30000

Hari TCS 50000

Input:

1. (EMPLOYEE ⋈ FACT_WORKERS)

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru nagar Hyderaba TCS 50000

d

An outer join is basically of three types:

 a. Left outer join
b. Right outer join
c. Full outer join

a. Left outer join:

o Left outer join contains the set of tuples of all combinations in R and S that
are equal on their common attribute names.
o In the left outer join, tuples in R have no matching tuples in S.
o It is denoted by ⟕.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:

1. EMPLOYEE ⟕ FACT_WORKERS
EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

b. Right outer join:

o Right outer join contains the set of tuples of all combinations in R and S that
are equal on their common attribute names.
o In right outer join, tuples in S have no matching tuples in R.
 o It is denoted by ⟖.

Example: Using the above EMPLOYEE table and FACT_WORKERS Relation

Input:

1. EMPLOYEE ⟖ FACT_WORKERS

Output:

EMP_NAME BRANCH SALARY STREET CITY

Ram Infosys 10000 Civil line Mumbai

Shyam Wipro 20000 Park street Kolkata

Hari TCS 50000 Nehru street Hyderabad

Kuber HCL 30000 NULL NULL

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

c. Full outer join:

o Full outer join is like a left or right join except that it contains all rows from
both tables.
o In full outer join, tuples in R that have no matching tuples in S and tuples in
S that have no matching tuples in R in their common attribute name.
o It is denoted by ⟗.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:
1. EMPLOYEE ⟗ FACT_WORKERS

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL

Kuber NULL NULL HCL 30000

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

3. Equi join:

It is also known as an inner join. It is the most common join. It is based on

matched data as per the equality condition. The equi join uses the comparison
operator(=).

Example:

CUSTOMER RELATION

CLASS_ID NAME
 1 John

2 Harry

3 Jackson

PRODUCT

PRODUCT_I CITY
D

1 Delhi

2 Mumbai

3 Noida

Input:

1. CUSTOMER ⋈ PRODUCT

Output:

CLASS_I NAME PRODUCT_ID CITY

D

1 John 1 Delhi

2 Harry 2 Mumbai
 3 Harry 3 Noida

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:
Relational Calculus

o Relational calculus is a non-procedural query language. In the non-

procedural query language, the user is concerned with the details of how to
obtain the end results.
o The relational calculus tells what to do but never explains how to do.

Types of Relational calculus:

1. Tuple Relational Calculus (TRC)

o The tuple relational calculus is specified to select the tuples in a relation. In

TRC, filtering variable uses the tuples of a relation.
 o The result of the relation can have one or more tuples.

Notation:

{T | P (T)} or {T | Condition (T)}

Where

T is the resulting tuples

P(T) is the condition used to fetch T.

For example:

{ T.name | Author(T) AND T.article = 'database' }

OUTPUT: This query selects the tuples from the AUTHOR relation. It returns a
tuple with 'name' from Author who has written an article on 'database'.

TRC (tuple relation calculus) can be quantified. In TRC, we can use Existential (∃)
and Universal Quantifiers (∀).

For example:

{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)}

Output: This query will yield the same result as the previous one.

2. Domain Relational Calculus (DRC)

o The second form of relation is known as Domain relational calculus. In

domain relational calculus, filtering variable uses the domain of attributes.

logical connectives ∧ (and), ∨ (or) and ┓ (not).

o Domain relational calculus uses the same operators as tuple calculus. It uses

o It uses Existential (∃) and Universal Quantifiers (∀) to bind the variable.

Notation:

1. { a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}

Where
 a1, a2 are attributes
P stands for formula built by inner attributes

For example:

1. {< article, page, subject > | ∈ javatpoint ∧ subject = 'database'}

Output: This query will yield the article, page, and subject from the relational
javatpoint, where the subject is a database.

Introduction to SQL
SQL is a standard language for accessing and manipulating databases.

What is SQL?
 SQL stands for Structured Query Language
 SQL lets you access and manipulate databases
 SQL became a standard of the American National Standards Institute
(ANSI) in 1986, and of the International Organization for Standardization
(ISO) in 1987

What Can SQL do?

 SQL can execute queries against a database
 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views
SQL is a Standard - BUT....
Although SQL is an ANSI/ISO standard, there are different versions of the SQL
language.

However, to be compliant with the ANSI standard, they all support at least the
major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a
similar manner.

Note: Most of the SQL database programs also have their own proprietary
extensions in addition to the SQL standard!

Using SQL in Your Web Site

To build a web site that shows data from a database, you will need:

 An RDBMS database program (i.e. MS Access, SQL Server, MySQL)

 To use a server-side scripting language, like PHP or ASP
 To use SQL to get the data you want
 To use HTML / CSS to style the page

RDBMS
RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS
SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a

collection of related data entries and it consists of columns and rows.

Look at the "Customers" table:

Example
SELECT * FROM Customers;

Some of The Most Important SQL Commands

 SELECT - extracts data from a database
  UPDATE - updates data in a database
 DELETE - deletes data from a database
 INSERT INTO - inserts new data into a database
 CREATE DATABASE - creates a new database
 ALTER DATABASE - modifies a database
 CREATE TABLE - creates a new table
 ALTER TABLE - modifies a table
 DROP TABLE - deletes a table
 CREATE INDEX - creates an index (search key)
 DROP INDEX - deletes an index

Data Types in Oracle Databases

Oracle Data Types

Oracle String data types

CHAR(size) It is used to store character data within the predefined length. It can
be stored up to 2000 bytes.

NCHAR(size) It is used to store national character data within the predefined length
It can be stored up to 2000 bytes.

VARCHAR2(size) It is used to store variable string data within the predefined length. It
can be stored up to 4000 byte.

VARCHAR(SIZE) It is the same as VARCHAR2(size). You can also use VARCHAR(size),

but it is suggested to use VARCHAR2(size)

NVARCHAR2(size) It is used to store Unicode string data within the predefined length. We
have to must specify the size of NVARCHAR2 data type. It can be
stored up to 4000 bytes.

Oracle Numeric Data Types

NUMBER(p, s) It contains precision p and scale s. The precision p can range from 1 to
38, and the scale s can range from -84 to 127.

FLOAT(p) It is a subtype of the NUMBER data type. The precision p can range
from 1 to 126.

BINARY_FLOAT It is used for binary precision( 32-bit). It requires 5 bytes, including

length byte.

BINARY_DOUBLE It is used for double binary precision (64-bit). It requires 9 bytes,

 including length byte.

Oracle Date and Time Data Types

DATE It is used to store a valid date-time format with a fixed length. Its range
varies from January 1, 4712 BC to December 31, 9999 AD.

TIMESTAMP It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format.

Oracle Large Object Data Types (LOB Types)

BLOB It is used to specify unstructured binary data. Its range goes up to 2 32-1 bytes
or 4 GB.

BFILE It is used to store binary data in an external file. Its range goes up to 2 32-1
bytes or 4 GB.

CLOB It is used for single-byte character data. Its range goes up to 2 32-1 bytes or 4
GB.

NCLOB It is used to specify single byte or fixed length multibyte national character set
(NCHAR) data. Its range is up to 232-1 bytes or 4 GB.

RAW(size) It is used to specify variable length raw binary data. Its range is up to 2000
bytes per row. Its maximum size must be specified.

LONG It is used to specify variable length raw binary data. Its range up to 2 31-1 bytes
RAW or 2 GB, per row.

SQL CREATE DATABASE Statement

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

Syntax
CREATE DATABASE databasename;
CREATE DATABASE Example
The following SQL statement creates a database called "testDB":

Example
CREATE DATABASE testDB;

SQL CREATE TABLE Statement

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

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

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g.
varchar, integer, date, etc.).

Tip: For an overview of the available data types, go to our complete Data Types
Reference.

SQL CREATE TABLE Example

The following example creates a table called "Persons" that contains five
columns: PersonID, LastName, FirstName, Address, and City:

Example
CREATE TABLE Persons (
PersonID int,
 LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Create Table Using Another Table

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns
can be selected.

If you create a new table using an existing table, the new table will be filled with
the existing values from the old table.

Syntax
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;

The following SQL creates a new table called "TestTables" (which is a copy of
the "Customers" table):

Example
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

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)
);

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together.

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

The table containing the foreign key is called the child table, and the table
containing the candidate 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 is used to prevent actions that would destroy links
between tables.

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

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 single column it allows 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;

INSERT INTO Example

The following SQL statement inserts a new record in the "Customers" table:

Example
INSERT INTO Customers (CustomerName, ContactName, Address, City,
PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen
21', 'Stavanger', '4006', 'Norway');

The selection from the "Customers" table will now look like this:

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the
"CustomerName", "City", and "Country" columns (CustomerID will be updated
automatically):

Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

SQL UPDATE Statement

The SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE Table
The following SQL statement updates the first customer (CustomerID = 1) with a
new contact person and a new city.

Example
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

SQL DELETE Statement

The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax
DELETE FROM table_name WHERE condition;

SQL DELETE Example

The following SQL statement deletes the customer "Alfreds Futterkiste" from the
"Customers" table:

Example
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

SQL SELECT Statement

The SQL SELECT Statement
The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax
SELECT column1, column2, ...
FROM table_name;

SELECT Column Example

The following SQL statement selects the "CustomerName" and "City" columns
from the "Customers" table:

Example
SELECT CustomerName, City FROM Customers;

The SQL WHERE Clause

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified
condition. WHERE Syntax

SELECT column1, column2, ...

FROM table_name
WHERE condition;

WHERE Clause Example

The following SQL statement selects all the customers from the country
"Mexico", in the "Customers" table:

Example
SELECT * FROM Customers
WHERE Country='Mexico';

Order By

Order by keyword sort the result-set either in ascending or descending order. This clause
sorts the result set in ascending order by default. In order to sort the result-set in
descending order DESC keyword is used.
Syntax

SELECT column_1, column_2, column_3………..

FROM Table_Name

ORDER BY column_1, column_2, column_3……. ASC|DESC;

Query

It sorts the records automatically in ascending order if we want to show the records in
descending order then we use DESC.

select * from employees ORDER BY salary;

Output

Group By

Group by statement is used to group the rows that have the same value. It is used with
aggregate functions for example AVG(), COUNT(), SUM()etc. One thing is to remember
about the group by clause that the tuples are grouped based on the similarity between the
attribute values of tuples.

Syntax

SELECT function_Name(column_1), column_2

FROM Table_Name

WHERE condition

GROUP BY column_1, col umn_2

ORDER BY column_1, column_2;

Function_Name: Name of the aggregate function, for example:

SUM(), AVG(), COUNT() etc.

Query

The COUNT() function is used when we need to return the total number of rows that are
stored in the database.So the example for the COUNT() function is

SELECT COUNT(Salary) from employee;

Output

SQL | Subquery

n SQL a Subquery can be simply defined as a query within another query. In other words
we can say that a Subquery is a query that is embedded in WHERE clause of another SQL
query. Important rules for Subqueries:

 In SQL a Subquery can be simply defined as a query within another query. In other
words we can say that a Subquery is a query that is embedded in WHERE clause of
another SQL query. Important rules for Subqueries:

 You can place the Subquery in a number of SQL

clauses: WHERE clause, HAVING clause, FROM clause. Subqueries can be used
with SELECT, UPDATE, INSERT, DELETE statements along with expression
operator. It could be equality operator or comparison operator such as =, >, =, <=
and Like operator.

 A subquery is a query within another query. The outer query is called as main
query and inner query is called as subquery.

 The subquery generally executes first when the subquery doesn’t have any co-
relation with the main query, when there is a co-relation the parser takes the
decision on the fly on which query to execute on precedence and uses the output of
the subquery accordingly.

 Subquery must be enclosed in parentheses.

 Subqueries are on the right side of the comparison operator.

 ORDER BY command cannot be used in a Subquery. GROUPBY command can be

used to perform same function as ORDER BY command.
  Use single-row operators with singlerow Subqueries. Use multiple-row operators
with multiple-row Subqueries.

 Syntax: There is not any general syntax for Subqueries. However, Subqueries are
seen to be used most frequently with SELECT statement as shown below:

SELECT column_name

FROM table_name

WHERE column_name expression operator

( SELECT COLUMN_NAME from TABLE_NAME WHERE ... );

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database.

REATE VIEW Syntax

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

SQL CREATE VIEW Examples

The following SQL creates a view that shows all customers from Brazil:

CREATE VIEW AAA As select * from emp WHERE Country = 'Brazil';

Select * from AAA;

What is Cursor in SQL ?

1. Cursor is a Temporary Memory or Temporary Work Station. It is Allocated

by Database Server at the Time of Performing DML(Data Manipulation Language)
operations on the Table by the User. Cursors are used to store Database Tables.
 2. There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors. These are
explained as following below.

3. Implicit Cursors: Implicit Cursors are also known as Default Cursors of SQL
SERVER. These Cursors are allocated by SQL SERVER when the user performs
DML operations.

4. Explicit Cursors: Explicit Cursors are Created by Users whenever the user requires
them. Explicit Cursors are used for Fetching data from Table in Row-By-Row
Manner.

5. DECLARE
6. total_rows number;
7. BEGIN
8. UPDATE Emp
9. SET Salary = Salary + 1500;
10.
11. total_rows := SQL%ROWCOUNT;
12.
13. dbms_output.put_line(total_rows || ' rows updated.');
14. END;

SQL Trigger
A trigger is a stored procedure in a database that automatically invokes whenever a special
event in the database occurs. For example, a trigger can be invoked when a row is inserted
into a specified table or when specific table columns are updated. In simple words, a trigger
is a collection of SQL statements with particular names that are stored in system memory.
It belongs to a specific class of stored procedures that are automatically invoked in
response to database server events. Every trigger has a table attached to it.

1. The following are the key differences between triggers and stored procedures:

2. Triggers cannot be manually invoked or executed.

2. There is no chance that triggers will receive parameters.

3. A transaction cannot be committed or rolled back inside a trigger.

4. Syntax:

5. create trigger [trigger_name]

6. [before | after]

7. {insert | update | delete}

8. on [table_name]

9. [for each row]

10. [trigger_body]

11. create trigger deep

on emp
for
insert,update ,delete
as
print 'you can not insert,update and delete this table i'
rollback;