UCS1401

DATABASE MANAGEMENT SYSTEMS

INTRODUCTION TO DBMS
 Introduction to Database Management
System
• Database management system consists of two parts:
– Database
– Management System
• Database:
– Data: Facts, figures, statistics etc. having no meaning.
– Record: Collection of related data items.
– Table or Relation: Collection of related records.
– The database is a collection of inter-related data.
 Why DBMS ? & What is DBMS ?
What is File System?
• A File Management system is a DBMS that allows
access to single files or tables at a time.
• In a File System, data is directly stored in set of files.
• It contains flat files that have no relation to other files
What is DBMS ?
• A database management system (DBMS) is
system software for creating and managing
databases.
• The DBMS provides users and programmers with
a systematic way to create, retrieve, update and
manage data.
 Drawbacks of File Processing System

• Data redundancy and inconsistency

• Difficulty in accessing data - conventional file-
processing environments do not allow needed data to be
retrieved in a convenient and efficient manner.
• Data isolation - scattered in various files
• Integrity problems - difficult to change the programs
(adding new constrains)
 Drawbacks of File Processing System

• Atomicity problems - inconsistent database state.

(Money transfer)
• Concurrent access anomalies - Many systems allow
multiple users to update the data simultaneously
• Security problems - Enforcing security constraints to
the file processing system is difficult
 Use of DBMS
• Effective and efficient management of data
• Query processing and management
• Easy to understand and user friendly
• Security and integrity of data
• Better Decision making
• Data sharing and storage
• Better access to accurate data
• Ensures error free information
 VIEWS OF DATA - DATA MODELS
• Purpose of a database system is to provide users with
an abstract view of the data.
– i.e the system hides certain details of how the data are
stored and maintained.

• Data Abstraction
– Physical level
• Describes how a record (e.g., customer) is
stored.
 VIEWS OF DATA - DATA MODELS

– Logical level
• Describes data stored in database, and the
relationships among the data.
– View level
• Application programs hide details of data types.
• Views can also hide information (e.g., salary)
for security purposes.
VIEWS OF DATA - DATA MODELS
 Data Models
• Data models define how the logical structure of a
database is modelled.
• It define how data is connected to each other and
how they are processed and stored inside the system.
• The very first data model could be flat data-models,
where all the data used are to be kept in the same
plane.
 Data Models
Types of Data Model
– There are basically two types of data model:
• Record based data model
• Object based data model
 1) Record based data model
• A fixed number of fields, or attributes in each record type and
each field is usually of a fixed length.
• The three most popular record-based data models are,

(i) Relational data model

(ii) Hierarchical data model
(iii) Network data model
 1) Record based data model
(i) Relational data model
– The relational data model uses tables to represent
the data and the relationships among these data.
– Each table has multiple columns and each column is
identified by a unique name.
 1) Record based data model
Advantages of Relational data model
• It is the simplest model at the conceptual level.

• The relational data model makes it easy to design,

implement, maintain, uses the database.
• It does not affect the DBMS’s capability to access the data.
• The main reason for the popularity of relational model is the
presence of powerful query capability.
1) Record based data model
 1) Record based data model
(ii) Hierarchical data model
• In the hierarchical data model, data are represented
by collections of records.
• Relationships among data are represented by links.
In this model, tree data structure is used.
• There are two concepts associated with the
hierarchical model segments types and parent-child
relationships.
 1) Record based data model
Advantages of Hierarchical data model
• Since the database is based on the hierarchical
structure the relationships between the various
layers are logically simple.
• The Hierarchical database model is based on the
parent-child relationships.
• It is very efficient one when the database contains
a large number of one-to-many relationships.
1) Record based data model
 1) Record based data model
(iii) Network data model

• In the network data model, data model data are

represented by collections of records.
• Relationships among data are represented by
links.
• In this data model, graph data structure is used.
• It permits a record to have more than one parent.
 1) Record based data model
Advantages of Network data model
• The Network data model is also conceptually simple and
easy to design.
• In the network data model relationships like one-to-one and
many-to-many are exist.
• In the network data model without the owner, no member
exists.
1) Record based data model
 2) Object based data model
• In the object-based data model, the database is
organized in real-world objects of several types.
• A number of fields or attributes are defined in each
object.
• The two most popular object-based data models are:

(i) Object oriented model

(ii) ER (Entity Relational model)
 2) Object based data model

(i) Object oriented model

• The object-oriented model is based on a
collection of objects.
• An object contains values stored in instances
variable within the object.
 2) Object based data model
Advantages of object oriented data model
• It represents relationships explicitly supporting both
navigated and associative access to information.
• Object-oriented database systems are not suited for
all applications.
Limitations:
• It is difficult to maintain when organizational
information changes.
 2) Object based data model

(ii) Entity Relationship Model (ER Model)

• Entity Relationship Model is a high-level data
model.
• This model is useful in developing a conceptual
design for the database.
• It is very simple and easy to design logical view of
data.
 2) Object based data model
Components of ER Model:
i) Entity:
• An entity may be any object, class, person or place.
• In the ER diagram, an entity can be represented as
rectangles.

• Weak Entity - An entity that depends on another

entity called a weak entity.
• The weak entity is represented by a double rectangle.
ii) Attribute
• The attribute is used to describe the property of an
entity.
• Ellipse is used to represent an attribute.
Key Attribute:
• The key attribute is used to represent the main
characteristics of an entity.
• It represents a primary key.
Composite Attribute:
• An attribute that composed of many other attributes is known
as a composite attribute.

Multivalued Attribute
• An attribute can have more than one value.
• The double oval is used to represent multivalued attribute.
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.
iii) Relationship
• A relationship is used to describe the relation
between entities.
• Diamond is used to represent the relationship.

Types:
• One-to-One Relationship
• One-to-many relationship
• Many-to-one relationship
• Many-to-many relationship
• One-to-One Relationship

• One-to-many relationship

• Many-to-one relationship

• Many-to-many relationship
 Database Languages
• A DBMS has appropriate languages and interfaces to express
database queries and updates.
• Database languages can be used to read, store and update the
data in the database.
• Types of Database Languages
– Data Definition Language (DDL)
– Data Manipulation Language (DML)
– Data Control Language (DCL)
– Transaction Control Language (TCL)
Database System Architecture
 Database System Architecture
Database Users:
Users are differentiated by the way they expect
to interact with the system.

• Naive User
• Application Programmers
• Sophisticated Users
• System Analyst
• Database Administrator
 Database System Architecture
Naive User:
They don’t have any DBMS knowledge, but they frequently
use the database.
Ex: Railway’s ticket booking users
Application Programmers:
• Also referred as System Analysts or simply Software
Engineers, are the back-end programmers who writes the
code for the application programs
• Application programmers design, debug, test, and maintain
set of programs.
 Database System Architecture
Sophisticated Users :
• Sophisticated users can be engineers, scientists,
business analyst, who are familiar with the database.
• They can develop their own database applications
according to their requirement.
System Analyst :
• System Analyst is a user who analyzes the
requirements of parametric end users.
• They check whether all the requirements of end users
are satisfied.
 Database System Architecture
Database Administrator
• 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
 Database System Architecture

Query processor:
Translates statements in a query language into low-
level instructions the database manager understands.
• DDL interpreter
• DDL compiler
• Query evaluation engine
 Database System Architecture
DDL interpreter
• This will interpret DDL statements and fetch the
definitions in the data dictionary.
DDL compiler:
• This will translate DML statements in a query
language into low level instructions that the
query evaluation engine understands.
Query evaluation engine
• This engine will execute low-level instructions
generated by the DML compiler on DBMS.
 Database System Architecture
Storage Manager/Storage Management:
A storage manager is a program module which acts
like interface between the data stored in a database and
the application programs and queries submitted to the
system.

• Authorization and integrity manager

• Transaction manager
• File manager
• Buffer manager
 Database System Architecture
Authorization and integrity manager
• Checks for integrity constraints and authority of users.
Transaction manager
• Ensures that the database remains in a consistent state
although there are system failures.
File manager:
• Manages the allocation of space on disk storage and the data
structures.
Buffer manager:
• It is responsible for retrieving data from disk storage into main
memory.
 Database System Architecture
• Data files: Stored in the database itself.
• Data dictionary: Stores metadata about the structure
of the database.
• Indices: Provide fast access to data items.
 Database System Architecture

Types of DBMS Architecture:

 Database System Architecture

Types of DBMS Architecture:

1-Tier Architecture
2-Tier Architecture
3-Tier Architecture
 Database System Architecture

Types of DBMS Architecture:

1-Tier Architecture
• The user can directly sit on the DBMS and uses
it.
• Any changes done here will directly be done on
the database itself.
• The 1-Tier architecture is used for development of
the local application.
 Database System Architecture
2-Tier Architecture:
• The 2-Tier architecture is same as basic client-server.
• The applications on the client end can directly
communicate with the database at the server side.
• For this interaction, API's like: ODBC, JDBC are used.
• The user interfaces and application programs are run on
the client-side.
• The server side is responsible to provide the
functionalities like: query processing and transaction
management.
 Database System Architecture

3-Tier Architecture
• The 3-Tier architecture contains another layer
between the client and server.
• The application on the client-end interacts with an
application server which further communicates
with the database system.
• End user has no idea about the existence of the
database beyond the application server.
Introduction to relational databases
 Introduction to relational databases
Relational databases
Data is represented in terms of tuples (rows) in
RDBMS.
What is table/Relation?
• Everything in a relational database is stored in the
form of relations.
• The RDBMS database uses tables to store data.
• A table is a collection of related data entries and
contains rows and columns to store data.
Properties of a Relation:
• Each relation has a unique name by which it is
identified in the database.
• Relation does not contain duplicate tuples.
• The tuples of a relation have no specific order.
• All attributes in a relation are atomic, i.e., each cell
of a relation contains exactly one value.
Database Schema
 Database Schema
• 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.
A database schema can be divided broadly into two
categories
1. Physical Database Schema
2. Logical Database Schema
Database Schema
 Database Schema
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.
Keys
 Keys
• Keys play an important role in the relational database.
• 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.
Types of key:
1. Primary key
2. Candidate key
3. Super Key
4. Foreign key
 Keys
Primary key:
• 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.
• The key which is most suitable from those lists become a
primary key.
Example:
 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.
 Keys
Primary key:
 Keys
Candidate key
• A candidate key is an attribute or set of an attribute which
can uniquely identify a tuple.
• The remaining attributes except for primary key are
considered as a candidate key.
• Key that consists of two or more
attributes that uniquely identify any record in a
table is called Composite key.
• The candidate keys are as strong as the primary key.
 Keys
Candidate keyExample
• 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.
 Keys
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.
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.
 Keys
Foreign key
• Foreign keys are the column of the table which is used to
point to the primary key of another table.
• 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.
 Keys
Foreign key
 Keys
Foreign key
Example
• We add the primary key of the DEPARTMENT table,
Department_Id as a new attribute in the EMPLOYEE
table.
• Now in the EMPLOYEE table, Department_Id is the
foreign key, and both the tables are related.
Relational Algebra
 Relational Algebra

• Relational algebra is a procedural query language.

• It collects instances of relations
as input and gives occurrences of relations as
output.
• It gives a step by step process to obtain the result of
the query.
• It uses operators to perform queries.
Relational Algebra
 Relational Algebra

• Unary Relational Operations

– SELECT (symbol: σ)
– PROJECT (symbol: π)
– RENAME (symbol: )

• Relational Algebra Operations From Set Theory

– UNION (υ)
– INTERSECTION ( ),
– DIFFERENCE (-)
– CARTESIAN PRODUCT ( x )
 Relational Algebra

• Binary Relational Operations

– JOIN
– DIVISION
 Relational Algebra

• Select Operation:
• The select operation selects tuples that satisfy a given
predicate.
• It is denoted by sigma (σ).

Syntax:
• σ p(R)

Example:
• σ BRANCH_NAME="perryride" (LOAN)
 Relational Algebra

• Project Operation:
• 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.
• It is denoted by ∏.

Syntax:
• ∏ A1, A2, An (r)
 Relational Algebra

Projection - Example
CustomerID CustomerName Status
1 Google Active
2 Amazon Active
3 Apple Inactive
4 Alibaba Active

Π CustomerName, Status (Customers)

CustomerName Status
Google Active
Amazon Active
Apple Inactive
Alibaba Active
 Relational Algebra
• Union Operation:
• Suppose there are two tuples R and S. The union operation
contains all the tuples that are either in R or S or both in R &
S.
• It eliminates the duplicate tuples.
• It is denoted by ∪.

Syntax:
• R∪S
• Duplicate tuples are eliminated automatically.
 Relational Algebra
Union - Example
Table A Table B
column column column column
1 2 1 2

1 1 1 1
1 2 1 3

Table A 𝖴 B
A 𝖴 B gives =>
column 1 column 2
1 1
1 2
1 3
 Relational Algebra
• Set Intersection:
• Suppose there are two tuples R and S. The set intersection
operation contains all tuples that are in both R & S.
• It is denoted by intersection ∩.

Syntax:
• R∩S
 Relational Algebra
Intersection - Example
Table A Table B
column column column column
1 2 1 2

1 1 1 1
1 2 1 3

Table A ∩ B
A ∩ B =>
column 1 column 2
1 1
 Relational Algebra
Cartesian product
• The Cartesian product is used to combine each row in one
table with each row in the other table.
• This type of operation is helpful to merge columns from
two relations.
• It is also known as a cross product.
• It is denoted by X.
Syntax:
EXD
 Relational Algebra
Cartesian product
• The Cartesian product is used to combine each row in one
table with each row in the other table.
• This type of operation is helpful to merge columns from
two relations.
• It is also known as a cross product.
• It is denoted by X.
Syntax:
EXD
 Relational Algebra
Cartesian Product - Example
Table A Table B

column column column column

1 2 1 2
1 1 1 1
1 2 1 3

• σ column 2 = '1' (A X B)
• Output – The above example shows all rows from relation A and
σ column 2 = '1' (A X B)
B whose column 2 has value 1 column 1 column 2
1 1
1 1
 Relational Algebra

Join Operations
• Join operation is essentially a
Cartesian product followed by a selection criterion.

• Join operation denoted by ⋈.

• JOIN operation also allows joining variously

related tuples from different relations.
 Relational Algebra

Join Operations: Types of JOIN

Inner Joins:
• Theta join
• EQUI join
• Natural join
Outer join:
• Left Outer Join
• Right Outer Join
• Full Outer Join
 Relational Algebra

Inner Join
• In an inner join, only those tuples that satisfy the
matching criteria are included, while the rest are
excluded.
 Relational Algebra
Theta Join
• The general case of JOIN operation is called a Theta join.
• It is denoted by symbol θ
Syntax
A ⋈θ B
Example

A ⋈ A.column 2 > B.column 2 (B)

Table A Table B
A ⋈ A.column 2 > B.column 2 (B)
column column column column
1 2 1 2 column 1 column 2

1 1 1 1 2 3
1 2 1 3
 Relational Algebra
EQUI join

• When a theta join uses only equivalence

condition, it becomes a equi join.
Example

A ⋈ A.column 2 = B.column 2 (B)

Table A Table B
A ⋈ A.column 2 = B.column 2 (B)
column column column column
1 2 1 2 column 1 column 2

1 1 1 1 1 1

1 2 1 3
 Relational Algebra
NATURAL JOIN (⋈)
• Natural join can only be performed if there is a
common attribute (column) between the
relations.
• The name and type of the attribute must be same.
C
Num Square
C⋈D
2 4
C⋈D Num Square Cube
3 9
2 4 4
D
3 9 9
Num Cube
2 8
3 18
SQL FUNDAMENTALS
 SQL FUNDAMENTALS

Types of SQL Fundamentals:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
 SQL FUNDAMENTALS

Data Definition Language (DDL)

• It is used to create a table, alter the structure of a
table and also drop the table.
1. Create command
2. Alter command
3. Drop command
4. Truncate command
 SQL FUNDAMENTALS
Data Definition Language (DDL)
1. Create command
• It is used to create a table.

Syntax
Create table <table name> ( columnname1 datatype1,
columnname2 datatype2, etc…);

Example
SQL> create table emp
(empno number(4), ename varchar2(30), salary
number(10,2), deptno number(2));
 SQL FUNDAMENTALS
Data Definition Language (DDL)
Alter Command
• It is used to add a new column or modify existing column
definitions.

Syntax
Alter table <table name>
Add ( new columnname1 datatype1,
newcolumnname2 datatype2, etc…);

Alter table <table name>

Modify ( oldcolumnname1 datatype1,
oldcolumnname2 datatype2, etc…);