Advanced Database

Management
Md. Manowarul Islam
Associate Professor, Dept. of CSE
Jagannath University
Contents:

❑Course Outline
❑Recommended textbook
❑Introduction to Database

Md. Manowarul Islam, Dept. of CSE, JnU

Theory Exam:

Number of class test :2 20

Number of Mid-term :1 30
Final Exam :1 30
Attendance :1 10
Assignment :1 10

Md. Manowarul Islam, Dept. of CSE, JnU

Database Management
Systems(DBMS)

Md. Manowarul Islam, Dept. of CSE, JnU

Database Systems Today

Md. Manowarul Islam, Dept. of CSE, JnU

Database Systems Today

Md. Manowarul Islam, Dept. of CSE, JnU

Database Systems Today

Md. Manowarul Islam, Dept. of CSE, JnU

What is a Database?

◼ “A set of information held in a computer”

Oxford English Dictionary
◼ “One or more large structured sets of
persistent data, usually associated with
software to update and query the data”
Free On-Line Dictionary of Computing
◼ “A collection of data arranged for ease and
speed of search and retrieval”
Dictionary.com

Md. Manowarul Islam, Dept. of CSE, JnU

Database Management Systems

◼ A DBMS is a collection of programs which

❑ provide management of databases
❑ control access to data
❑ contain a query language to retrieve information
easily DBMS

Application
program End-user
Md. Manowarul Islam, Dept. of CSE, JnU
Database Management Systems

◼ DBMS contains information about a

particular enterprise
❑ Collection of interrelated data
❑ Set of programs to access the data
❑ This data is called database
DBMS

Application
program End-user
Md. Manowarul Islam, Dept. of CSE, JnU
DBMS:
✓ DBMS contains information about a particular enterprise
❑ Collection of interrelated data
❑ Set of programs to access the data
❑ This data is called database

✓Goals of DBMS
❑ Provide an environment that is both convenient and
efficient
❑ Manage large bodies of information
❑ Ensure the safety and security of the information
❑ Avoid possible anomalous results

Md. Manowarul Islam, Dept. of CSE, JnU

DBMS:
✓ Application
❑ Banking: All transactions

Md. Manowarul Islam, Dept. of CSE, JnU

DBMS:
✓ Application

❑Airlines: Reservations, schedules

Md. Manowarul Islam, Dept. of CSE, JnU

DBMS:
✓ Application
❑Universities: Registration, grades

Md. Manowarul Islam, Dept. of CSE, JnU

DBMS:
✓ Application
❑Sales: Customers, products, purchases

Md. Manowarul Islam, Dept. of CSE, JnU

DBMS:
✓ Application
❑Online retailers: Order tracking, customized recommendations

Md. Manowarul Islam, Dept. of CSE, JnU

DBMS:
✓ Application
❑Manufacturing: Production, inventory, orders, supply chain

Md. Manowarul Islam, Dept. of CSE, JnU

DBMS:
✓ Application
❑Human resources: Employee records, salaries, tax deductions

Databases touch all

aspects of our lives

Md. Manowarul Islam, Dept. of CSE, JnU

Purpose of DBMS:
✓ Ordinary file system has a number of major drawbacks
1. Data redundancy and inconsistency
❑ Multiple file formats, duplication of information in
different files
❑Higher storage and access cost
❑ Duplicate data leads to data inconsistency

Md. Manowarul Islam, Dept. of CSE, JnU

Purpose of DBMS:
✓ Ordinary file system has a number of major drawbacks
1. Difficulty in accessing data
❑ Need to write a new program to carry out each new
task

Md. Manowarul Islam, Dept. of CSE, JnU

Purpose of DBMS:
✓ Ordinary file system has a number of major drawbacks

3. Data isolation
❑ Multiple file formats, duplication of information in
different files
❑Writing new application programs to retrieve the
appropriate data is difficult.

Md. Manowarul Islam, Dept. of CSE, JnU

Purpose of DBMS:
✓ Ordinary file system has a number of major drawbacks

4. Integrity problems
❑ Integrity constraints (e.g. account balance > 0) become
“buried” in program code rather than being stated explicitly
❑ Hard to add new constraints or change existing ones

Md. Manowarul Islam, Dept. of CSE, JnU

Purpose of DBMS:
✓ Ordinary file system has a number of major drawbacks
5. Atomicity problems
❑ Failures may leave database in an inconsistent state
with partial updates carried out
❑ Example: Transfer of funds from one account to
another should either complete or not happen at all

Md. Manowarul Islam, Dept. of CSE, JnU

Purpose of DBMS:
✓ Ordinary file system has a number of major drawbacks
6. Concurrent-access anomalies
❑ Concurrent accessed needed for performance
❑ Uncontrolled concurrent accesses can lead to
inconsistencies
❑Example: Two people reading a balance and updating it at
the same time

Md. Manowarul Islam, Dept. of CSE, JnU

Purpose of DBMS:

✓ Ordinary file system has a number of major drawbacks

7. Security problems
❑ Hard to provide user access to some, but not all, data
❑ every user of the database system should be able to access
all the data

✓Database systems offer solutions to all the above problems

Md. Manowarul Islam, Dept. of CSE, JnU

Data abstraction:
✓ Database designer need to use complex data structure
✓All user are not computer trained-hide the complexity

✓Level of abstraction
❑ Physical level
❑ Logical level
❑ View level

Md. Manowarul Islam, Dept. of CSE, JnU

Data abstraction:
✓Physical level
❑ Describes how data are actually stored
❑ Describes complex low-level data structures in detail

✓ Logical level
❑ Describes what data stored in database
❑ Describes what relationships exist among those data

✓ View Level
❑Describe only part of the entire database.
❑Views can also hide information (such as an employee’s
salary) for security purposes.

Md. Manowarul Islam, Dept. of CSE, JnU

Data abstraction:

Md. Manowarul Islam, Dept. of CSE, JnU

Instance and Schemas:

✓Schemas
❑ The overall design of the database
❑ Schemas are changed infrequently
❑Variable declaration (with type definition) of a
programming language
✓ Instance
❑ Actual content of the database at a particular point in time
❑ Analogous to the value of a variable

Md. Manowarul Islam, Dept. of CSE, JnU

Some Commercial Database Management
Systems
◼ Oracle – Oracle 8i, Oracle9i, Oracle 10g
◼ Microsoft SQL Server
◼ IBM DB2/DB2UDB
◼ Informix
◼ Sybase
◼ MySQL
◼ Ingress
◼ PostgreSQL

Md. Manowarul Islam, Dept. of CSE, JnU

For Personal Computers

◼ Microsoft Access
◼ FoxPro
◼ dBase

Md. Manowarul Islam, Dept. of CSE, JnU

Contents:

❑ Data Model
❑ Database Language

Md. Manowarul Islam, Dept. of CSE, JnU

Data Independence in Relational Databases

Md. Manowarul Islam, Dept. of CSE, JnU

Data Management
User/Application

Query Query Query

Data

DataBase Management System (DBMS)

Md. Manowarul Islam, Dept. of CSE, JnU

Example: At a Company
Query 1: Is there an employee named “Nemo”?
Query 2: What is “Nemo’s” salary?
Query 3: How many departments are there in the company?
Query 4: What is the name of “Nemo’s” department?
Query 5: How many employees are there in the
“Accounts” department?
Employee Department
ID Name DeptID Salary … ID Name …
10 Nemo 12 120K … 12 IT …
20 Dory 156 79K … 34 Accounts …
40 Gill 89 76K … 89 HR …
52 Ray 34 85K … 156 Marketing …
… … … … … … … …

Md. Manowarul Islam, Dept. of CSE, JnU

DataBase Management System (DBMS)

High-level
Query Q Answer

Translates Q into
best execution plan
DBMS for current conditions,
runs plan

Data

Md. Manowarul Islam, Dept. of CSE, JnU

 Example: Store that Sells Cars
Owners of Make Model OwnerID ID Name Age
Honda Accords Honda Accord 12 12 Nemo 22
who are <= Honda Accord 156 156 Dory 21
23 years old
Join (Cars.OwnerID = Owners.ID)

Filter (Make = Honda and Filter (Age <= 23)

Model = Accord)
Cars Owners
Make Model OwnerID ID Name Age
Honda Accord 12 12 Nemo 22
Toyota Camry 34 34 Ray 42
Mini Cooper 89 89 Gill 36
Honda Accord 156 156 Dory 21
… … … … … …
Md. Manowarul Islam, Dept. of CSE, JnU
DataBase Management System (DBMS)

High-level
Query Q Answer

Translates Q into
best execution plan
DBMS for current conditions,
runs plan
Keeps data safe
and correct
despite failures,
concurrent Data
updates, online
processing, etc.
Md. Manowarul Islam, Dept. of CSE, JnU
Describing Data: Data Models

◼ A data model is a collection of concepts for

describing data.
◼ A schema is a description of a particular
collection of data, using a given data model.
◼ The relational model of data is the most widely
used model today.
❑ Main concept: relation, basically a table with rows and
columns.
❑ Every relation has a schema, which describes the
columns (fields, attributes) and keys.

Md. Manowarul Islam, Dept. of CSE, JnU

Example: University Database
◼ Schema:
❑ Students(sid text,
name text,
login text,
age integer,
gpa float)

❑ Courses(cid text,
cname text,
credits integer)

❑ Enrolled(sid text,
cid text,
grade text)
Md. Manowarul Islam, Dept. of CSE, JnU
Example of a Database Schema

Md. Manowarul Islam, Dept. of CSE, JnU

 Example of a database state
STUDENT
Name Student_number Class Major

Smith 17 1 CS

Brown 8 2 CS

Md. Manowarul Islam, Dept. of CSE, JnU

Levels of Abstraction Users

◼ Views describe how

users see the data.
◼ Conceptual schema View 1 View 2 View 3
defines logical
structure Conceptual Schema
◼ Physical schema Physical Schema
describes the files
and indexes used.
DB

Md. Manowarul Islam, Dept. of CSE, JnU

Database Architecture With Views

Md. Manowarul Islam, Dept. of CSE, JnU

Data Model
✓ A collection of conceptual tools for describing
❑ Data
❑ Data relationship
❑Consistency constraints
✓ Provides a way to describe the design of a database
at the physical, logical and view level
✓Four different categories:
❑ Relational Model
❑ Entity-Relationship Model
❑ Object-Based Data Model
❑ Semi structured data model

Md. Manowarul Islam, Dept. of CSE, JnU

Relational Model
✓ It uses a collection of tables to represent both data and
relationships among those data.
✓Each table has multiple columns, and each column has a
unique name
✓An example of a record-based model
• database is structured in fixed-format records of
several types
•Each table contains records of a particular type
•Each record type defines a fixed no. of fields, or
attributes

✓ Widely used data model and a vast majority of

current database systems are based on the relational
model.

Md. Manowarul Islam, Dept. of CSE, JnU

Relational Model

Md. Manowarul Islam, Dept. of CSE, JnU

Entity-Relationship Model

✓ Models an enterprise as a collection of entities and

relationships
▪ Entity: a “thing” or “object” in the enterprise that is
distinguishable from other objects
▪ Relationship: an association among several entities
✓Widely used in database design
✓Represented diagrammatically by an entity-relationship
diagram

Md. Manowarul Islam, Dept. of CSE, JnU

Entity-Relationship Model

Md. Manowarul Islam, Dept. of CSE, JnU

DBMS Languages

◼ Data Definition Language (DDL)

◼ Data Manipulation Language (DML)
❑ High-Level or Non-procedural Languages: These
include the relational language SQL
◼ May be used in a standalone way or may be embedded
in a programming language
❑ Low Level or Procedural Languages:
◼ These must be embedded in a programming language

Slide 1- 50
Md. Manowarul Islam, Dept. of CSE, JnU
Data Manipulation Language (DML)
✓ Language for accessing and manipulating the data organized
by the appropriate data model
✓DML also known as query language
✓Two classes of languages
▪ Procedural – user specifies what data is required and how to
get those data. e.g. Relational Algebra.
▪ Declarative (nonprocedural) – user specifies what data is
required without specifying how to get those data. e.g. SQL

✓Declarative DMLs are usually easier to learn and use than

procedural DMLs.

Md. Manowarul Islam, Dept. of CSE, JnU

Data Manipulation Language (DML)
✓ The types of accesses are
✓DML also known as query language
i) The retrieval of information stored in the database - Query
ii) The insertion of new information into the database - insert
iii) The deletion of information from the database - delete
iv) The modification of information stored in the database - update

Md. Manowarul Islam, Dept. of CSE, JnU

Data-Definition Language (DDL)
✓ Specify the database schemas
✓The DDL is also used to specify additional properties of data.
✓ Example
create table account (
account_number char(10),
balance number (12,2)
);
✓Data definition and data manipulation languages are not two
separate language; they simply part of a single database
language such as SQL.

Md. Manowarul Islam, Dept. of CSE, JnU

Database architecture
◼ A Database Architecture is a representation of DBMS
design.
◼ It helps to design, develop, implement, and maintain the
database management system.
◼ A DBMS architecture allows dividing the database
system into individual components that can be
independently modified, changed, replaced, and altered.

Md. Manowarul Islam, Dept. of CSE, JnU

 Significance of “Tiers”

N-tier architectures have the same

components
o Presentation
o Business/Logic
o Data

N-tier architectures try to separate the

components into different tiers/layers
o Tier: physical separation
o Layer: logical separation

Md. Manowarul Islam, Dept. of CSE, JnU

Significance of “Tiers”

Md. Manowarul Islam, Dept. of CSE, JnU

 The 3-Tier Architecture for Web Apps

o Presentation Layer
Static or dynamically generated content rendered by the
browser (front-end)

Md. Manowarul Islam, Dept. of CSE, JnU

 The 3-Tier Architecture for Web Apps

o Logic Layer
o A dynamic content processing and generation level
application server, e.g., Java EE, ASP.NET, PHP
o Business logic acts as an interface between Client layer
and Data Access Layer
o All business logic – like validation of data, calculations,
data insertion/modification are written under business logic
layer.
o It makes communication faster and easier between the
client and data layer

Md. Manowarul Islam, Dept. of CSE, JnU

 The 3-Tier Architecture for Web Apps

o Data Layer
A database, comprising both data sets and the database
management system or RDBMS software that manages
and provides access to the data (back-end)

Md. Manowarul Islam, Dept. of CSE, JnU

 Significance of “Tiers”

Database runs on Server

o Separated from client
o Easy to switch to a different database

Presentation and logic layers still tightly connected

o Heavy load on server
o Potential congestion on network
o Presentation still tied to business logic

Md. Manowarul Islam, Dept. of CSE, JnU

 1-Tier Architecture

All 3 layers are on the same machine

o All code and processing kept on a single machine

Presentation, Logic, Data layers are tightly connected

o Scalability: Single processor means hard to increase volume of
processing
o Portability: Moving to a new machine may mean rewriting
everything
o Maintenance: Changing one layer requires changing other layers

Md. Manowarul Islam, Dept. of CSE, JnU

 2-Tier Architecture

Database runs on Server

o Separated from client
o Easy to switch to a different database
Presentation and logic layers still tightly connected (coupled)
o Heavy load on server
o Potential congestion on network
o Presentation still tied to business logic

Md. Manowarul Islam, Dept. of CSE, JnU

 3-Tier Architecture

o Each layer can potentially run on a different machine

o Presentation, logic, data layers disconnected

Md. Manowarul Islam, Dept. of CSE, JnU

A Typical 3-tier Architecture
Architecture Principles
o Client-server architecture
o Each tier (Presentation,
Logic, Data) should be
independent.
o Change in platform affects
only the layer running on
that particular platform

Md. Manowarul Islam, Dept. of CSE, JnU

A Typical 3-tier Architecture

Presentation Layer
o Provides user interface
o Handles the interaction
with the user
o Sometimes called the
GUI or client view or
front-end
o Should not contain
business logic or data
access code

Md. Manowarul Islam, Dept. of CSE, JnU

A Typical 3-tier Architecture

Logic Layer
o The set of rules for
processing information
o Can accommodate many
users
o Sometimes called
middleware/ back-end
o Should not contain
presentation or data
access code

Md. Manowarul Islam, Dept. of CSE, JnU

A Typical 3-tier Architecture

Data Layer
o The physical storage
layer for data
persistence
o Manages access to DB
or file system
o Sometimes called back-
end
o Should not contain
presentation or business
logic code

Md. Manowarul Islam, Dept. of CSE, JnU

 3-Tier Architecture - Advantages

Independence of Layers
o Easier to maintain
o Components are reusable
o Faster development (division of work)
o Web designer does presentation
o Software engineer does logic
o DB admin does data model

Md. Manowarul Islam, Dept. of CSE, JnU

Database Users
Users are differentiated by the way they expect to interact with
the system
◼ Application programmers – interact with system through DML calls

◼ Sophisticated users – form requests in a database query language

◼ Specialized users – write specialized database applications that do

not fit into the traditional data processing framework
◼ Naïve users – invoke one of the permanent application programs
that have been written previously
❑ Examples, people accessing database over the web, bank tellers,
clerical staff

Md. Manowarul Islam, Dept. of CSE, JnU

Database Administrator

◼ Coordinates all the activities of the database system; the database

administrator has a good understanding of the enterprise’s
information resources and needs.
◼ Database administrator's duties include:
❑ Schema definition
❑ Storage structure and access method definition
❑ Schema and physical organization modification
❑ Granting user authority to access the database
❑ Specifying integrity constraints
❑ Acting as liaison with users
❑ Monitoring performance and responding to changes in
requirements

Md. Manowarul Islam, Dept. of CSE, JnU

Introduction to SQL

Md. Manowarul Islam, Dept. of CSE, JnU

Contents:

❑ SQL
❑ Domain type in SQL
❑ Creating a Database
❑ Operation on database

Md. Manowarul Islam, Dept. of CSE, JnU

What is a relational database?
◼ Relational DB
❑ Relational Database Management System (RDBMS)
consists of
◼ A set of tables
◼ A schema
◼ A schema:
❑ is a description of data in terms of data model
❑ Defines tables and their attributes

Md. Manowarul Islam, Dept. of CSE, JnU

What is a relational database?
◼ Tables ≡ relation:
❑ Stores information about an entity or theme–Consist of
columns (fields) and rows (records).
❑ Rows ≡ tuple, describing information about a single item,
eg. A specific student
❑ columns ≡ attributes, describing a single characteristic
(attributes) of its item, eg. Its ID number, GPA, etc
❑ Every row is unique & identified by a key

Md. Manowarul Islam, Dept. of CSE, JnU

Example

Md. Manowarul Islam, Dept. of CSE, JnU

Data Types & Basics
◼ Data Types
❑ Integers: TinyInt, SmallInt, MediumInt, Int, BigInt
❑ Real Numbers: Float, Double, Decimal, Real
❑ Time: Date, DateTime, Timestamp, Time,
❑ String: Char, VarChar
❑ Text: TinyText, MediumText, LongText

CSE 498 Day 3 - Basics of

Md. Manowarul Islam, Dept. of CSE, JnU
MySQL
Query Language

✓ A query language is a language in which a user requests

information from the database.
❑ Procedural Language
❑ Nonprocedural Language

Md. Manowarul Islam, Dept. of CSE, JnU

Structured Query Language(SQL)

✓ SQL is the most influential commercially marketed query

language.
✓This is not just query language
✓It can define the structure of the data, modify data in the
database and specify security constraints
✓SQL has become the standard relational database language

Md. Manowarul Islam, Dept. of CSE, JnU

SQL

✓ SQL language has several parts:

❑ DDL
❑ DML
❑ Integrity
❑ View definition
❑ Transaction control
❑ Embedded SQL
❑ Authorization

Md. Manowarul Islam, Dept. of CSE, JnU

Data Definition Language (DDL)

✓ Specifies the set of relations in a database

✓ It keeps the following information about each relation such as:
❖The schema for each relation
❖ The domain of values associated with each attribute.
❖ Integrity constraints
❖ The set of indices to be maintained for each relations
❖ Security and authorization information for each relation.
❖The physical storage structure of each relation on disk.

Md. Manowarul Islam, Dept. of CSE, JnU

 Basic Domain Types
✓ A FIXED length string
✓ The size parameter specifies the column length in
characters
char(n) ✓ Can be from 0 to 255.
✓ Can contain letters, numbers, and special characters
✓ branch_city char(30),

✓ A VARIABLE length string

✓ The size parameter specifies the maximum column
length in characters
VARCHAR(n) ✓ Can be from 0 to 65535
✓ Can contain letters, numbers, and special
characters).
✓ name VARCHAR(20)
TEXT(size) Holds a string with a maximum length of 65,535 bytes

Md. Manowarul Islam, Dept. of CSE, JnU

Basic Domain Types

Md. Manowarul Islam, Dept. of CSE, JnU

 Basic Domain Types
✓ A floating-point number.
✓ display length (M) and the number of decimals (D).
FLOAT(M,D) −
✓ Decimal precision M can go to 24 places for a
FLOAT.

✓ M precision that represents the number of significant

digits.
✓ The range of M is 1 to 65.
DECIMAL(M,D)
✓ The range of D is 0 and 30.
✓ DECIMAL(19,9) has 9 digits for the fractional part
and 19-9 = 10 digits for integer part.

Md. Manowarul Islam, Dept. of CSE, JnU

Basic Domain Types

Md. Manowarul Islam, Dept. of CSE, JnU

SQL
❑ SQL CREATE DATABASE Statement
➢CREATE DATABASE database_name
➢ Example: CREATE DATABASE my_db
❑SQL CREATE table Statement
➢We define a relation by create table command
➢In easy way:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
);

Md. Manowarul Islam, Dept. of CSE, JnU

SQL

❑SQL Insert data Statement

➢INSERT INTO table_name
VALUES (value1, value2, value3,...);
➢Example:
➢INSERT INTO branch
VALUES (‘Mogbajar', ‘Dhaka', 1200);

Md. Manowarul Islam, Dept. of CSE, JnU

The CREATE DATABASE Statement

◼ CREATE DATABASE bcctestdb;

Md. Manowarul Islam, Dept. of CSE, JnU

The DROP DATABASE Statement

◼ CREATE DATABASE bcctestdb;

Md. Manowarul Islam, Dept. of CSE, JnU

The show DATABASE Statement

◼ show databases;

Md. Manowarul Islam, Dept. of CSE, JnU

The USE Statement

◼ use bcc;

Md. Manowarul Islam, Dept. of CSE, JnU

The show tables Statement

◼ Show tables;

Md. Manowarul Islam, Dept. of CSE, JnU

SQL CREATE table Statement

◼ To create a table called "Persons" that contains five

columns: P_Id, LastName, FirstName, Address, and
City.
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Md. Manowarul Islam, Dept. of CSE, JnU

 SQL CREATE table Statement
create table person(
PID int,
name varchar(30)
);

Md. Manowarul Islam, Dept. of CSE, JnU

The show tables Statement

◼ Show tables;

Md. Manowarul Islam, Dept. of CSE, JnU

The DESCRIBE tablename Statement

◼ DESCRIBE person;

Md. Manowarul Islam, Dept. of CSE, JnU

 The DROP TABLE Statement
◼ The DROP TABLE statement is used to
delete a table.
◼ DROP TABLE table_name;

Md. Manowarul Islam, Dept. of CSE, JnU

 The DROP TABLE Statement
◼ drop table person;

Md. Manowarul Islam, Dept. of CSE, JnU

The ALTER TABLE Statement

◼ The ALTER TABLE statement is used to add,

delete, or modify columns in an existing table.

Md. Manowarul Islam, Dept. of CSE, JnU

Add attribute or column

◼ To add a column in a table, use the following syntax:

❑ ALTER TABLE table_name
ADD column_name datatype;

Md. Manowarul Islam, Dept. of CSE, JnU

Add attribute or column

◼ ALTER TABLE person

ADD Email varchar(255);

Md. Manowarul Islam, Dept. of CSE, JnU

Add attribute or column

◼ ALTER TABLE person

ADD Email varchar(255);

Md. Manowarul Islam, Dept. of CSE, JnU

The INSERT INTO Statement

◼ The INSERT INTO statement is used to insert a new row

in a table.

INSERT INTO table_name

VALUES (value1, value2, value3,...)

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...)

Md. Manowarul Islam, Dept. of CSE, JnU

INSERT INTO Example

insert into person

VALUES(1001,'Manowar', 'dhaka')

SELECT * from person

Md. Manowarul Islam, Dept. of CSE, JnU

INSERT INTO Example

insert into person(PID,name,address)

VALUES(1002, 'Biswajit', 'Sylhet')

SELECT * from person

Md. Manowarul Islam, Dept. of CSE, JnU

Constraint

Md. Manowarul Islam, Dept. of CSE, JnU

SQL Constraints

◼ Constraints are used to limit the type of data that

can go into a table.
◼ Constraints can be specified
❑ when a table is created
❑ with the CREATE TABLE statement
◼ Constraints can be specified after the table is
created
❑ with the ALTER TABLE statement

Md. Manowarul Islam, Dept. of CSE, JnU

SQL Constraints

◼ We will focus on the following constraints:

❑ NOT NULL
❑ UNIQUE
❑ PRIMARY KEY
❑ FOREIGN KEY
❑ CHECK
❑ DEFAULT

Md. Manowarul Islam, Dept. of CSE, JnU

SQL Constraints
Constraint Description
NOT NULL Specifies that the column cannot contain a
null value
UNIQUE Specifies a column or combination of
columns whose values must be unique for all
rows in the table
PRIMARY KEY Uniquely identifies each row of the table
FOREIGN KEY Establishes and enforces a foreign key
relationship between the column and a
column of the referenced table
CHECK Specifies a condition that must be true

Md. Manowarul Islam, Dept. of CSE, JnU

Creating Constraints

◼ When
❑ During table creation
❑ After table creation, by modifying the existing
table
◼ How
❑ Column level approach
❑ Table level approach

Md. Manowarul Islam, Dept. of CSE, JnU

Creating Constraints

Constraint Level Description

Column level References a single column and is defined within
a specification for the owning column; can define
any type of integrity constraint

Table level References one or more columns and is defined

separately from the definitions of the columns in
the table; can define any constraints except NOT
NULL

Md. Manowarul Islam, Dept. of CSE, JnU

Example

CREATE TABLE Product (

name CHAR(30) PRIMARY KEY,
category VARCHAR(20))

OR:
CREATE TABLE Product (
name CHAR(30),
category VARCHAR(20)
PRIMARY KEY (name))

Md. Manowarul Islam, Dept. of CSE, JnU

Example

CREATE TABLE Product (

name CHAR(30),
category VARCHAR(20),
price INT,
PRIMARY KEY (name, category))

Md. Manowarul Islam, Dept. of CSE, JnU

Example

◼ CREATE TABLE DEPT

( DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP
ON DELETE SET DEFAULT ON UPDATE CASCADE );

Md. Manowarul Islam, Dept. of CSE, JnU

SQL NOT NULL Constraint

◼ The NOT NULL constraint enforces a column to NOT

accept NULL values.
◼ Can only be created at column level
◼ The NOT NULL constraint enforces a field to always
contain a value.
❑ you cannot insert a new record, or update a record without
adding a value to this field.

Md. Manowarul Islam, Dept. of CSE, JnU

Example…

◼ If a constraint is being created at the column level, the

constraint applies to the column specified
◼ Look the following example.
create table Persons( describe persons

PersonId int NOT NULL,

FirstName varchar(100) NOT NULL,
LastName varchar(100) NOT NULL,
age int
)

Md. Manowarul Islam, Dept. of CSE, JnU

Example…

◼ Insert some rows:

insert into persons (PersonId) values (12345)

insert into persons (PersonId, FirstName, LastName, age)

values (12346, 'Manowar', 'Islam', 29)

insert into persons(PersonId,FirstName,LastName,age)

values (12345,'','',23)

Md. Manowarul Islam, Dept. of CSE, JnU

Example…

◼ Insert NULL values

insert into persons values (12345, NULL, NULL, 23)

Md. Manowarul Islam, Dept. of CSE, JnU

Example…

◼ insert into persons values('Anwar','Hossain', 45)

◼ insert into persons (PersonId)values()

Md. Manowarul Islam, Dept. of CSE, JnU

Add using ALTER

◼ ALTER TABLE persons MODIFY age int

NOT NULL

Md. Manowarul Islam, Dept. of CSE, JnU

Add using ALTER

◼ ALTER TABLE persons MODIFY age int

NULL

Md. Manowarul Islam, Dept. of CSE, JnU

SQL UNIQUE Constraint

◼ The UNIQUE constraint uniquely identifies each record

in a database table.
◼ The UNIQUE provides a guarantee for uniqueness for a
column or set of columns.

Column level
Table level

Md. Manowarul Islam, Dept. of CSE, JnU

SQL UNIQUE Constraint

◼ We can also put a name of the key.

❑ [CONSTRAINT constraint_name]
UNIQUE(column_list)
◼ If you define a UNIQUE constraint without specifying a
name, MySQL automatically generates a name for it.
CREATE TABLE student (
StudentID INT,
name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL,
CONSTRAINT uc_ID UNIQUE (StudentID)
);

Md. Manowarul Islam, Dept. of CSE, JnU

SQL UNIQUE Constraint
CREATE TABLE Persons
◼ Example (
P_Id int UNIQUE,
LastName varchar(255) NOT NULL,
City varchar(255)
)

CREATE TABLE Persons

(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

Md. Manowarul Islam, Dept. of CSE, JnU

SQL UNIQUE Constraint
UNIQUE constraint
EMPLOYEES

INSERT INTO

Allowed
Not allowed:
already exists

Md. Manowarul Islam, Dept. of CSE, JnU

Let start with an example
create table Student(
StudentID int UNIQUE,
Name varchar(100),
Adress varchar(100)
)

describe student;

Md. Manowarul Islam, Dept. of CSE, JnU

Insert item

insert into student values(11,'Manowar', 'Dhaka')

insert into student values(11,'Manowar', 'Dhaka')

Md. Manowarul Islam, Dept. of CSE, JnU

Insert item

insert into student values(12,'Manowar', 'Dhaka')

insert into student values(NULL,'Manowar', 'Dhaka')

Md. Manowarul Islam, Dept. of CSE, JnU

Insert item

select * from student

Md. Manowarul Islam, Dept. of CSE, JnU

Add after creating table

◼ Suppose you have column with duplicate

value.
◼ You cannot make it unique using alter table

ALTER TABLE
student
ADD UNIQUE
(Name)

Md. Manowarul Islam, Dept. of CSE, JnU

Add after creating table

◼ First add one column name email.

◼ ALTER TABLE table_name
ADD new_column_name column_definition
ALTER TABLE student
select * from student
ADD email varchar(100)

Md. Manowarul Islam, Dept. of CSE, JnU

Create

◼ Now alter and add unique key

ALTER TABLE student
ADD UNIQUE (email)

insert into student (Name, Adress) values('Anwar','Rangpur')

Md. Manowarul Islam, Dept. of CSE, JnU

Insert
insert into student
values(123,'Anwar','Rangpur', 'anwar@gmail.com')

insert into student

values(123,'Anwar','Rangpur', 'anwar@gmail.com')

Md. Manowarul Islam, Dept. of CSE, JnU

Insert duplicate
insert into student
values(124,'Anwar','Rangpur', 'anwar@gmail.com')

Md. Manowarul Islam, Dept. of CSE, JnU

Show INDEX

◼ show INDEX from student

Md. Manowarul Islam, Dept. of CSE, JnU

DROP index

◼ DROP INDEX index_name ON table_name;

DROP INDEX email ON student

Md. Manowarul Islam, Dept. of CSE, JnU

Lets give a name

◼ You can add the name of constraint

ALTER TABLE Student

ADD CONSTRAINT uc_EID UNIQUE (email)

Md. Manowarul Islam, Dept. of CSE, JnU

Show the index
ALTER TABLE Student
ADD CONSTRAINT uc_EID UNIQUE (email)

Show index from student

Md. Manowarul Islam, Dept. of CSE, JnU

DROP a UNIQUE Constraint

◼ To drop a UNIQUE constraint, use the following SQL

ALTER TABLE student

DROP CONSTRAINT uc_EID

Show index from student

Md. Manowarul Islam, Dept. of CSE, JnU

Adding Constraints to Existing Tables

◼ Constraints are added to an existing table

with the ALTER TABLE command
◼ Add a NOT NULL constraint using MODIFY
clause
◼ All other constraints are added using ADD
clause

Oracle 11g: SQL

Md. Manowarul Islam, Dept. of CSE, JnU
Change column name

◼ Create a table
create table std (
id int NOT NULL )

ALTER TABLE table_name

CHANGE COLUMN old_name new_name column_definition

ALTER TABLE std

CHANGE COLUMN id StdID int NOT NULL

Md. Manowarul Islam, Dept. of CSE, JnU

 End

Md. Manowarul Islam, Dept. of CSE, JnU