2) a) database ---A database is an organized collection of data, so that it can be

easily accessed and manage. A database is an organized collection of

structured information, or data, typically stored electronically in a computer
system. A database is usually controlled by a database management system
(DBMS)
Data within the most common types of databases in operation today is
typically modeled in rows and columns in a series of tables to make
processing and data querying efficient.
Advantages of Database Management System
1. Better use of data or information - We can easily and efficiently access
well-managed and synchronized forms of data with the help of DBMS.
2. Secured Data - Data management systems (DBMS) offer a stronger
framework for data privacy and security policies, assisting businesses in
enhancing data security.
3. Reduces Data Inconsistency and Redundancy-- Data Redundancy and
inconsistency can both be significantly decreased by properly designing a
database with the help of a database management system.
4. Better Recovery and Backups - Backup and recovery are handled
automatically by the DBMS. Users don't need to regularly back up their data
because the DBMS handles this for them
5. Fast Data Sharing - Database administration makes it possible for
consumers to access more and better-managed data.
6. Helps in decision-making - Because of the well-managed data and
improved data access provided by DBMS, we are able to produce better-
quality information and, as a result, make better judgments
7. User Friendly - Data are presented in a straightforward and logical
manner by database management systems (DBMS). It is simple to carry out
many activities, such as the addition, deletion, or creation of files or
data.
8. Data Abstraction - In order to give users an abstract overview of the data,
database systems are primarily used.
Disadvantages of DBMS
1. High Cost: Implementing a DBMS can be expensive due to the cost of
licensing, hardware, and maintenance.
2. Complexity: DBMS is a complex software that requires a significant
amount of technical expertise to install, configure, and maintain.
3. System Overhead: DBMS requires system resources such as memory,
CPU, and disk space, which can lead to system overhead and reduced
system performance.
4. Data Dependency: DBMS stores data in a particular format, which can
create data dependency issues.
5. Single Point of Failure: DBMS represents a single point of failure for an
organization's data.
6. Huge Dimensions-- For database management software to work correctly,
a lot of disc space is needed.
2) b) database :-- Database Management Systems (DBMS) are software systems
used to store, retrieve, and run queries on data. A DBMS serves as an
interface between an end-user and a database, allowing users to create, read,
update, and delete data in the database.
DBMS manage the data, the database engine, and the database schema,
allowing for data to be manipulated or extracted by users and other programs.
This helps provide data security, data integrity, concurrency, and uniform data
administration procedures.
DBMS offer many benefits over traditional file systems, including flexibility
and a more complex backup system.
File System :-- A file system is a technique of arranging the files in a storage medium like
a hard disk, pen drive, DVD, etc. It helps you to organizes the data and allows easy
retrieval of files when they are required. It mostly consists of different types of files like
mp3, mp4, txt, doc, etc. that are grouped into directories.
A file system enables you to handle the way of reading and writing data to the storage
medium. It is directly installed into the computer with the Operating systems such as
Windows and Linux.
Difference between File System and DBMS:
Basis File System DBMS

The file system is a way of arranging

DBMS is software for managing
the files in a storage medium within a
the database.
Structure computer.

Data Redundant data can be present in a file In DBMS there is no redundant

Redundancy system. data.

Backup and It doesn’t provide backup and It provides backup and recovery
Recovery recovery of data if it is lost. of data even if it is lost.

Query There is no efficient query processing Efficient query processing is there

processing in the file system. in DBMS.

There is more data consistency

There is less data consistency in the
because of the process of
file system.
Consistency normalization.

It has more complexity in

It is less complex as compared to
handling as compared to the file
DBMS.
Complexity system.

Security File systems provide less security in DBMS has more security
Constraints comparison to DBMS. mechanisms as compared to file
 Basis File System DBMS

systems.

It has a comparatively higher cost

It is less expensive than DBMS.
Cost than a file system.

3)a) Architecture of DBMS:--

-> The DBMS design depends upon its architecture. The basic client/server
architecture is used to deal with a large number of PCs, web servers, database
servers and other components that are connected with networks.
o The client/server architecture consists of many PCs and a workstation which
are connected via the network.
o DBMS architecture depends upon how users are connected to the database to
get their request done.

1-Tier Architecture
o In this architecture, the database is directly available to the user. It means the
user can directly sit on the DBMS and uses it.
o Any changes done here will directly be done on the database itself. It doesn't
provide a handy tool for end users.
o The 1-Tier architecture is used for development of the local application, where
programmers can directly communicate with the database for the quick
response.
2-Tier Architecture
o The 2-Tier architecture is same as basic client-server. In the two-tier
architecture, 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.
o The user interfaces and application programs are run on the client-side.
o The server side is responsible to provide the functionalities like: query
processing and transaction management
o To communicate with the DBMS, client-side application establishes a
connection with the server side.
 3-Tier Architecture
o The 3-Tier architecture contains another layer between the client and server.
In this architecture, client can't directly communicate with the server.
o The application on the client-end interacts with an application server which
further communicates with the database system.
o End user has no idea about the existence of the database beyond the
application server. The database also has no idea about any other user
beyond the application.
o The 3-Tier architecture is used in case of large web application.

3)b) level of schema architecture:--

 Conceptual schema
 The conceptual schema (sometimes called the logical schema) describes the stored data in terms
of the data model of the DBMS.
 The conceptual level is often known as the logical level.
 Conceptual level is used for describing the entire database with its structure.
 the conceptual schema describes all relations that are stored in the database.
 Internal schema
 The internal level consists of an internal schema which is used to describe the
physical structure of the database in which the data is stored.
 This schema is often called as the physical schema.
 The physical schema specifies additional storage details.
 The internal level uses the physical data model. This data model describes the
method by which the data should be stored in a block.
 Complex low-level data structures are described in detail in this schema
o External schemas
 External schemas, which usually are also in terms of the data model of the DBMS, allow data
access to be customized (and authorized) at the level of individual users or groups of users.
 Any given database has exactly one conceptual schema and one physical schema because it has
just one set of stored relations, but it may have several external schemas
 Each external schema consists of a collection of one or more views and relations from the
conceptual schema.
 The external schema design is guided by end user requirements.
 The view schema describes the end user interaction with database syste

f) relational database:--
 A relational database is a collection of relations with distinct relation names.
 The relational database schema is the collection of schemas for the relations in the database.
e.g.-- a university database with relations called Students, Faculty, Courses, Rooms, Enrolled,
Teaches, and Meets In.
 An instance of a relational database is a collection of relation instances, one per relation schema
in the database schema; of course, each relation instance must satisfy the domain constraints in
its schema.
 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.
 Relational databases are used to track inventories, process ecommerce
transactions, manage huge amounts of mission-critical customer information, and
much more.
 Today, the advantages of the relational model continue to make it the most widely
accepted model for databases.

o G) hierarchical databases

In the late 1960s, IBM developed the Information Management System (IMS) DBMS, used even
today in many major installations. IMS formed the basis for an alternative data representation
framework called the hierarchical data model.
Hierarchical databases are well-suited to the tape
storage systems used by mainframe computers in the 1970s. It was quite popular among
organizations using this storage system back then.
 However, it lost popularity after the introduction of the relational model. The 1990s saw some
resurgence of the hierarchical database system through XML.

e.g.-- IBM’s Information Management System (IMS) is an example of a hierarchical database

system. Windows Registry is another such example. Another example that you may be
aware of is XML data storage .

Pros of Hierarchical Model

 A tree-like structure is incredibly straightforward and quick to navigate.
 Any modification to the parent node is reflected automatically in the child node, ensuring
data integrity.

Cons of Hierarchical Model

 Relationships that are complex are not supported.

 Because it only supports one parent per child node, if we have a complex relationship in
which a child node needs to have two parents, we won’t be able to describe it using this
model.
 When a parent node is removed, the child node is removed as well.
5. Entity
o An entity is an object in the real world that is distinguishable from other objects.
o It is often useful to identify a collection of similar entities. Such a collection is called
an entity set.
o An entity is described using a set of attributes.
o Consider an organization as an example- manager, product, employee,
department etc. can be taken as an entity

Attributes:--
 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.


Relationship of ER model
The entity-relationship (ER) data model allows us to describe the data involved in a
real-world
Relationship----
enterprise in terms of objects and their relationships and is widely used to develop an initial
database design. The ER model is important primarily for its role in database design. It
provides useful concepts that allow us to move from an informal description of what users
want from their database to a more detailed, and precise, description that can be implemented
in a DBMS. An Entity Relationship Diagram (ER Diagram) pictorially explains the
relationship between entities to be stored in a database. ER diagram is created
based on three principal components: entities, attributes, and relationships.
 The diamond shape showcases a relationship in the ER diagram.
 It depicts the relationship between two entities.
  In the example below, both the student and the course are entities, and study is the
relationship between them.

6) JOIN:- The join operation is one of the most useful operations in relational algebra
and is the most commonly used way to combine information from two or more
relations. Although a join can be defined as a cross-product followed by selections and
projections, joins arise much more frequently in practice than plain cross-products.
Further, the result of a cross-product is typically much larger than the result of a join,
and it is very important to recognize joins and implement them without materializing
the underlying cross-product
A Join can be broadly divided into two types:
Inner Join- Inner Join is a join that can be used to return all the values that
have matching values in both the tables.

The inner join can be further divided into the following types:
) Equi Join:-- Equi Join is an inner join that uses the equivalence condition
for fetching the values of two tables.
The MySQL query for equi join can be as follow s:
Select employee.empId, employee.empName, department.deptName from
employee Inner Join department on employee.deptId = department.deptId;
) Natural Join:-- Natural Join is an inner join that returns the values of
the two tables on the basis of a common attribute that has the same
name and domain. It does not use any comparison operator. It also
removes the duplicate attribute from the results.
The MySQL query for natural join can be as follows:
Select * from employee Natural Join department;
2)Outer Join:-- Outer Join is a join that can be used to return the records in
both the tables whether it has matching records in both the tables or not.
The outer join can be further divided into three types:
1. Left-Outer Join :-- The Left-Outer Join is an outer join that returns all the
values of the left table, and the values of the right table that has matching
values in the left table. If there is no matching result in the right table, it
 will return null values in that field.

2. Right-Outer Join :-- The Right-Outer Join is an outer join that returns all
the values of the right table, and the values of the left table that has

matching values in the right table.

3. Full-Outer Join:-- The Full-Outer join contains all the values of both the
tables whether they have matching values in them or not.
7. Data independence :-- Data Independence is defined as a property of DBMS that
helps you to change the Database schema at one level of a database system without
requiring to change the schema at the next higher level. Data independence helps you to
keep data separated from all programs that make use of it.
You can use this stored data for computing and presentation. In many systems,
data independence is an essential function for components of the system.
In DBMS there are two types of data independence
1. Physical data independence
2. Logical data independence.

Physical Data Independence

 Physical data independence helps you to separate conceptual levels from the
internal/physical levels.

 It allows you to provide a logical description of the database without the need to
specify physical structures.

 Compared to Logical Independence, it is easy to achieve physical data independence.

 you can easily change the physical storage structures or devices with an effect on the
conceptual schema.

 Physical data independence is achieved by the presence of the internal level of the
database
 Logical Data Independence
o Logical Data Independence is the ability to change the conceptual scheme without
changing
1. External views
2. External API or programs
o Any change made will be absorbed by the mapping between external and conceptual
levels.
o When compared to Physical Data independence, it is challenging to achieve logical
data independence.

Difference between Physical and Logical Data Independence

Logica Data Independence Physical Data Independence

Logical Data Independence is mainly concerned

Mainly concerned with the storage of the data.
with the structure or changing the data definition.

It is difficult as the retrieving of data is mainly

It is easy to retrieve.
dependent on the logical structure of data.

Compared to Logic Physical independence it is Compared to Logical Independence it is easy

difficult to achieve logical data independence. achieve acc achieve data independence.

A change in the physical level usually does n

You need to make changes in the Application
program if new fields are added or deleted from need change at the Application program
the database.
level.

Modifications made at the internal levels

Modification at the logical levels is significant
whenever the logical structures of the database may or May’t be needed to improve
are changed.
the performance of the structure.

Concerned with conceptual schema Concerned with internal schema

Example: change in compression

Example: Add/Modify/Delete a new attribute techniques,hashing algorithms,

storage devices, etc

8) Concurrency control with locking method:--

Locks are an integral part to maintain concurrency control in DBMS. A transaction in any
system implementing lock based concurrency control cannot read or write a statement
until it has obtained the required locks.
There are two types of locks in Lock based protocols. These are:
 Binary Locks - These can only be in one of two states, locked or unlocked.
  Shared/Exclusive Locks - Shared locks are acquired when only read operation is
to be performed. Shared locks can be shared between multiple transactions as
there is no data being altered. Exclusive locks are used when write operation is
performed. Only the transaction holding the exclusive lock is allowed to make
changes to the data value.
The different locking protocols are −
Simplistic Lock Protocol
A lock is obtained by the transaction on the data value before the write operation is
performed. After the write operation, the lock can be released.
Two-Phase Locking Protocol
The two-phase locking protocol has two phases, namely the growing and shrinking
phase. The transaction can only acquire locks when it is in the growing phase. When it
enters the shrinking phase, it can release the previously acquired locks but cannot
acquire new locks. The exclusive locks are represented by X and the shared locks are
represented by s.
Rigorous two-phase locking protocol
Rigorous two phase locking protocol is merely an extension of two phase locking protocol
and strict two phase locking protocol. Here, all the locks held by a transaction, whether
shared or exclusive, are only released once the transaction commits or aborts.
Strict Two-Phase Locking Protocol
Strict two phase locking protocol is similar to two phase locking protocol. The only
difference is that in strict 2PL protocol all the exclusive locks acquired by the protocol
need to be held until the protocol either commits or aborts
9. ER diagram for hospital with the set of patients and a set of medical doctors associated with
each patients along of the various tests and examininations conducteed
Relational Model:
Entities

Relationships

ADD COMMENT
4) draw an ER diagram for university consisting database consisting of four entities

a)student b) department c) class d) faculty and convert into tables