UCS1401 - DATABASE MANAGEMENT SYSTEMS

INSTITUTE VISION AND MISSION

VISION OF THE INSTITUTE:
To achieve a prominent position among the top technical institutions.

MISSION OF THE INSTIITUTE:

M1: To bestow standard technical education par excellence through state of the art
infrastructure, competent faculty and High ethical standards.

M2: To nurture research and entrepreneurial skills among students in cutting edge
technologies.

M3: To provide education for developing high-quality professionals to transform the

society.
DEPARTMENT VISION AND MISSION

VISION OF THE DEPARTMENT:

To create eminent professionals of Computer Science and Engineering by imparting quality education.

MISSION OF THE DEPARTMENT:

M1: To provide technical exposure in the field of Computer Science and Engineering through state of

the art infrastructure and ethical standards.

M2: To engage the students in research and development activities in the field of Computer Science

and Engineering.

M3: To empower the learners to involve in industrial and multi-disciplinary projects for addressing

the societal needs.

PROGRAM EDUCATIONAL OBJECTIVES (PEOs):

Our graduates shall

PEO1: Analyse, design and create innovative products for addressing social needs.

PEO2: Equip themselves for employability, higher studies and research.

PEO3: Nurture the leadership qualities and entrepreneurial skills for their successful career

PROGRAM SPECIFIC OUTCOMES (PSOs):

Students will be able to

PSO1: Apply the basic and advanced knowledge in developing software, hardware and firmware solutions

addressing real life problems.

PSO2: Design, develop, test and implement product-based solutions for their career enhancement.
PROGRAM OUTCOMES:
PO1 Engineering knowledge
PO2 Problem analysis
PO3 Design/development of solutions
PO4 Conduct investigations of complex problems
PO5 Modern tool usage
PO6 The engineer and society
PO7 Environment and sustainability
PO8 Ethics
PO9 Individual and team work
PO10 Communication
PO11 Project management and finance
PO12 Life-long learning
OBJECTIVES:
• To understand the basic principles of database management system.
• To access database using SQL queries.
• To design database using E-R model.
• To understand the internal storage structure and query processing.
• To have an introduction to advanced developments in database models and
applications.
COURSE OUTCOMES:
Upon completion of the course, the students will be able to
1. Analyze the basic database management systems.
2. Map the ER model to relational model to design database.
3. Analyze the storage structure and hashing of databases.
4. Write queries using normalization and optimize them.
5. Appraise the advanced database models and the applications.
 UNIT I INTRODUCTION

Database management systems – view of data – database languages - database

design – database and application architecture – database users and
administrators – relational databases – database schema – keys – schema
diagrams – relational query language – centralized and client server architecture
for 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.

• Management System
– The management system is important because without the existence of some kind
of rules and regulations it is not possible to maintain the database.

• Definition:
– A Database Management System (DBMS) is a software system that is designed to
manage and organize data in a structured manner. It allows users to create,
modify, and query a database, as well as manage the security and access controls
for that database.
 Why DBMS ? What is DBMS ?
• What is File System?
– The file system is basically a way of arranging the files in a storage medium like
a hard disk.
– The file system organizes the files and helps in the retrieval of files when they are
required.
– File systems consist of different files which are grouped into directories.
– The directories further contain other folders and files.
NAME OF THE FILE DETAILS
SYSTEM
File Allocation Table (FAT) 1. Used in Microsoft's MS-DOS
2. still used in some devices like digital cameras

New Technology File 1. developed by Microsoft.

System (NTFS) 2. includes features like file compression, disk quotas, and file
encryption.
Hierarchical File System 1. developed by Apple for use in its Macintosh computers
(HFS) 2. which makes it easier to organize and find files.
ext (extended file system) 1. used by Linux.
ZFS (Zettabyte File 1. developed by Sun Microsystems for use in its Solaris OS.
System)
CD/DVD/Blu ray disks 1. ISO 9660
Pen drive / Android Mobile 1. FAT/exFAT/NTFS/ext4
• What is DBMS ?
– Database Management System is basically software that manages the collection of
related data.
– It is used for storing data and retrieving the data effectively when it is needed.
– It also provides proper security measures for protecting the data from unauthorized
access.
– In Database Management System the data can be fetched by SQL queries and
relational algebra.
– It also provides mechanisms for data recovery and data backup.
 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 constraints)

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

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

– 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.
(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.
• 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.
 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)
(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.
– 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.
(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.

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 and application architecture
database and application architecture
• Database Users:
• Users are differentiated by the way they expect to interact with the system.
– Naïve 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.
• 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 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
 Query processor

• Translates statements in a query language into low-level instructions the database manager
understands.
• 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.
 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.
• The storage manager components include:
– 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.
• Data files: Stored in the database itself.
• Data dictionary: Stores metadata about the structure of the database.
• Indices: Provide fast access to data items.
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.

• 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.
• 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
• 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:
o Each relation has a unique name by which it is identified in the database.

o Relation does not contain duplicate tuples.

o The tuples of a relation have no specific order.

o All attributes in a relation are atomic, i.e., each cell of a relation contains exactly one value.
 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 −
• 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 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:
– Primary key
– Candidate key
– Super Key
– Foreign key
• 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.
– 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.
• 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.
– The candidate keys are as strong as the primary key.
– 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.
• 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.
– 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.
• 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.

– 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 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:
• For example: LOAN Relation
• 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)
• 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)
– Example:
• ∏ NAME, CITY (CUSTOMER)
• Union Operation:
– Suppose there are two relations 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.
Example Query: ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)
• Set Intersection:
– Suppose there are two relations R and S. The set intersection operation contains all tuples that
are in both R & S.
– It is denoted by intersection ∩.
– Syntax:
• R∩S

• Set Difference:
– Suppose there are two relations R and S. The set intersection operation contains all tuples that
are in R but not in S.
– It is denoted by intersection minus (-).
• Syntax:
• R-S
• Cartesian product
– 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.
– It is denoted by X.
– Syntax:
• EXD
• Rename Operation:
• The rename operation is used to rename the output relation.
• It is denoted by rho (ρ).
• ρ(STUDENT1, STUDENT)
 Centralized and Client-Server Architecture for DBMS

• A database management system (DBMS) is a software system that is designed to

manage and organize data in a structured manner.
• Two of the most used architectures in DBMS are centralized and client-server
architectures.
– Centralized Architecture
– Client-Server Architecture
 Centralized Architecture
• A centralized architecture for DBMS is one in which all data is stored on a single
server, and all clients connect to that server in order to access and manipulate the
data.
• Advantages:
– Simplicity - there is only one server to manage, and all clients use the same data.

• Drawbacks:
– Server can become a bottleneck as the number of clients and/or the amount of data increases.

• Example:
– SQLite
 Client-Server Architecture
• A client-server architecture for DBMS is one in which data is stored on a central
server, but clients connect to that server in order to access and manipulate the data.
• This type of architecture is more complex than a centralized architecture, but it offers
several advantages over the latter.
• Advantages:

– More scalable than a centralized architecture.

– More fault-tolerant than a centralized architecture

• Example:
– MySQL, Oracle
E-R Diagram for Banking
 Applications
• Railway Reservation System
• Banking
• Credit card exchanges
• Online Shopping
• Airline Reservation System
• Military
• Social Media Sites