2022-2023

Group-A (Very Short Answer Type Question)

1. Answer any ten of the following :

(I) What is an Instance of a Database?

An instance of a database is the set of data stored in the database at a specific
moment in time.

(II) What is the good database management practice?

Good database management practices include regular backups, normalization,
access control, and performance tuning.

(III) What is a relation in RDBMS?

A relation in RDBMS is a table consisting of rows (tuples) and columns (attributes).

(IV) _____ is the time of temporal data that record when a fact was recorded in a
database? Transaction time

(V) What is candidate key?

A candidate key is a minimal set of attributes that can uniquely identify a tuple in
a relation.

(VI) If the database modifications occur while the transaction is still active, the
transaction is said to use the _____ modification technique? immediate
modification
(VII) What is view?
A view is a virtual table derived from one or more tables or view

(VIII) The view of total database content is _____? conceptual schema.

(IX) In an E-R diagram attributes are represented by _____? ellipses.

(X) ____________ clause is an additional filter that is applied to the result.

HAVING

(XI) What do you mean by Data independence?

Data independence means that changes in the schema at one level do not affect
the schema at the next higher level.

(XII) Which forms are based on the concept of transitive functional dependency
_____? Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF) are based
on the concept of transitive functional dependency.
 Group-B (Short Answer Type Question)
Answer any three of the following
2. What is a View ? View does not take any memory space. Justify. How do you
create an Insertable and Updatable view ?
A View is a virtual table in a database, defined by a SQL query. It displays data
from one or more base tables or other views without storing the data physically.
Justification: View Does Not Take Memory Space
A view does not occupy memory because it does not store data. Instead, it stores
only the SQL query definition. When you query the view, the database
dynamically executes the query on the underlying tables and retrieves the results.

Creating an Insertable and Updatable View

To make a view insertable and updatable, follow these rules:
1. Base on a Single Table: The view should be created from one table only.
2. Include Key Columns: Include the primary key and all NOT NULL columns of
the base table.
3. Avoid Aggregates and Joins: Do not use aggregate functions, groupings, or
joins.
Example
3. What is Database? Explain the advantages of DBMS over File Oriented System?
DBMS: A Database Management System (DBMS) is application software that
allows users to efficiently define, create, maintain and share databases. Defining a
database involves specifying the data types, structures and constraints of the data
to be stored in the database. Creating a database involves storing the data on
some storage medium that is controlled by DBMS. Maintaining a database
involves updating the database whenever required to evolve and reflect changes
in the miniworld and also generating reports for each change. Sharing a database
involves allowing multiple users to access the database. DBMS also serves as an
interface between the database and end users or application programs. It
provides control access to the data and ensures that data is consistent and correct
by defining rules on them.
An application program accesses the database by sending queries or requests for
data to the DBMS. A query causes some data to be retrieved from the database.
Advantages of DBMS over File system:
• Data redundancy and inconsistency: Redundancy is the concept of
repetition of data i.e. each data may have more than a single copy. The file
system cannot control the redundancy of data as each user defines and
maintains the needed files for a specific application to run. There may be a
possibility that two users are maintaining the data of the same file for
different applications. Hence changes made by one user do not reflect in
files used by second users, which leads to inconsistency of data. Whereas
DBMS controls redundancy by maintaining a single repository of data that is
defined once and is accessed by many users. As there is no or less
redundancy, data remains consistent.
• Data sharing: The file system does not allow sharing of data or sharing is
too complex. Whereas in DBMS, data can be shared easily due to a
centralized system.
• Data concurrency: Concurrent access to data means more than one user is
accessing the same data at the same time. Anomalies occur when changes
made by one user get lost because of changes made by another user. The
file system does not provide any procedure to stop anomalies. Whereas
DBMS provides a locking system to stop anomalies to occur.
• Data searching: For every search operation performed on the file system, a
different application program has to be written. While DBMS provides
inbuilt searching operations. The user only has to write a small query to
retrieve data from the database.
• Data integrity: There may be cases when some constraints need to be
applied to the data before inserting it into the database. The file system
does not provide any procedure to check these constraints automatically.
Whereas DBMS maintains data integrity by enforcing user-defined
constraints on data by itself.
• System crashing: In some cases, systems might have crashed due to various
reasons. It is a bane in the case of file systems because once the system
 crashes, there will be no recovery of the data that’s been lost. A DBMS will
have the recovery manager which retrieves the data making it another
advantage over file systems.
• Data security: A file system provides a password mechanism to protect the
database but how long can the password be protected? No one can
guarantee that. This doesn’t happen in the case of DBMS. DBMS has
specialized features that help provide shielding to its data.
• Backup: It creates a backup subsystem to restore the data if required.
• Interfaces : It provides different multiple user interfaces like graphical user
interface and application program interface.
• Easy Maintenance : It is easily maintainable due to its centralized nature.

4. Explain various types of Data Models in DB systems?

Types of Relational Models
1. Conceptual Data Model
2. Representational Data Model
3. Physical Data Model
It is basically classified into 3 types:-
1. Conceptual Data Model
The conceptual data model describes the database at a very high level and is
useful to understand the needs or requirements of the database. It is this model,
that is used in the requirement-gathering process i.e. before the Database
Designers start making a particular database. One such popular model is
the entity/relationship model (ER model). The E/R model specializes in entities,
relationships, and even attributes that are used by database designers. In terms of
this concept, a discussion can be made even with non-computer science(non-
technical) users and stakeholders, and their requirements can be understood.
Entity-Relationship Model( ER Model): It is a high-level data model which is used
to define the data and the relationships between them. It is basically a conceptual
design of any database which is easy to design the view of data.
Components of ER Model:
1. Entity: An entity is referred to as a real-world object. It can be a name,
place, object, class, etc. These are represented by a rectangle in an ER
Diagram.
2. Attributes: An attribute can be defined as the description of the entity.
These are represented by Ellipse in an ER Diagram. It can be Age, Roll
Number, or Marks for a Student.
3. Relationship: Relationships are used to define relations among different
entities. Diamonds and Rhombus are used to show Relationships.
Characteristics of a conceptual data model
• Offers Organization-wide coverage of the business concepts.
• This type of Data Models are designed and developed for a business
audience.
• The conceptual model is developed independently of hardware
specifications like data storage capacity, location or software specifications
like DBMS vendor and technology. The focus is to represent data as a user
will see it in the “real world.”
Conceptual data models known as Domain models create a common vocabulary
for all stakeholders by establishing basic concepts and scope
2. Representational Data Model
This type of data model is used to represent only the logical part of the database
and does not represent the physical structure of the database. The
representational data model allows us to focus primarily, on the design part of the
database. A popular representational model is a Relational model. The relational
Model consists of Relational Algebra and Relational Calculus. In the Relational
Model, we basically use tables to represent our data and the relationships
between them. It is a theoretical concept whose practical implementation is done
in Physical Data Model.
The advantage of using a Representational data model is to provide a foundation
to form the base for the Physical model
3. Physical Data Model
The physical Data Model is used to practically implement Relational Data Model.
Ultimately, all data in a database is stored physically on a secondary storage device
such as discs and tapes. This is stored in the form of files, records, and certain
other data structures. It has all the information on the format in which the files
are present and the structure of the databases, the presence of external data
structures, and their relation to each other. Here, we basically save tables in
memory so they can be accessed efficiently. In order to come up with a good
physical model, we have to work on the relational model in a better
way. Structured Query Language (SQL) is used to practically implement Relational
Algebra.
This Data Model describes HOW the system will be implemented using a specific
DBMS system. This model is typically created by DBA and developers. The purpose
is actual implementation of the database.
Characteristics of a physical data model:
• The physical data model describes data need for a single project or
application though it maybe integrated with other physical data models
based on project scope.
 • Data Model contains relationships between tables that which addresses
cardinality and nullability of the relationships.
• Developed for a specific version of a DBMS, location, data storage or
technology to be used in the project.
• Columns should have exact datatypes, lengths assigned and default values.
• Primary and Foreign keys, views, indexes, access profiles, and
authorizations, etc. are defined
Some Other Data Models
1. Hierarchical Model
The hierarchical Model is one of the oldest models in the data model which was
developed by IBM, in the 1950s. In a hierarchical model, data are viewed as a
collection of tables, or we can say segments that form a hierarchical relation. In
this, the data is organized into a tree-like structure where each record consists of
one parent record and many children. Even if the segments are connected as a
chain-like structure by logical associations, then the instant structure can be a fan
structure with multiple branches. We call the illogical associations as directional
associations.
2. Network Model
The Network Model was formalized by the Database Task group in the 1960s. This
model is the generalization of the hierarchical model. This model can consist of
multiple parent segments and these segments are grouped as levels but there
exists a logical association between the segments belonging to any level. Mostly,
there exists a many-to-many logical association between any of the two segments.
3. Object-Oriented Data Model
In the Object-Oriented Data Model, data and their relationships are contained in a
single structure which is referred to as an object in this data model. In this, real-
world problems are represented as objects with different attributes. All objects
have multiple relationships between them. Basically, it is a combination of Object
Oriented programming and a Relational Database Model.
4. Float Data Model
The float data model basically consists of a two-dimensional array of data models
that do not contain any duplicate elements in the array. This data model has one
drawback it cannot store a large amount of data that is the tables can not be of
large size.
5. Context Data Model
The Context data model is simply a data model which consists of more than one
data model. For example, the Context data model consists of ER Model, Object-
Oriented Data Model, etc. This model allows users to do more than one thing
which each individual data model can do.

5. What is the Degree of Relationships?

Degree of Relationship
In DBMS, a degree of relationship represents the number of entity types that are
associated with a relationship. For example, we have two entities, one is a student
and the other is a bag and they are connected with the primary key and foreign
key. So, here we can see that the degree of relationship is 2 as 2 entities are
associating in a relationship.
Types of degree
Now, based on the number of linked entity types, we have 4 types of degrees of
relationships.
1. Unary
2. Binary
3. Ternary
4. N-ary
6. What is 2NF?Give Example.
Second Normal Form
The second Normal Form (2NF) is based on the concept of fully functional
dependency. The second Normal Form applies to relations with composite keys,
that is, relations with a primary key composed of two or more attributes. A
relation with a single-attribute primary key is automatically in at least 2NF. A
relation that is not in 2NF may suffer from the update anomalies. To be in the
second normal form, a relation must be in the first normal form and the relation
must not contain any partial dependency. A relation is in 2NF if it has no partial
dependency, i.e., no non-prime attribute (attributes that are not part of any
candidate key), which is dependent on any proper subset of any candidate key in
the table. In other words,
A relation that is in First Normal Form and every non-primary-key attribute is fully
functionally dependent on the primary key, then the relation is in Second Normal
Form (2NF).
Note – If the proper subset of the candidate key determines a non-prime
attribute, it is called partial dependency . The normalization of 1NF relations to
2NF involves the removal of partial dependencies . If a partial dependency exists,
we remove the partially dependent attribute(s) from the relation by placing them
in a new relation along with a copy of their determinant. Consider the examples
given below.
Example-1: Consider the table below.
STUD_NO COURSE_NO COURSE_FEE
1 C1 1000
2 C2 1500
1 C4 2000
4 C3 1000
4 C1 1000
2 C5 2000
{Note that, there are many courses having the same course fee. } Here,
COURSE_FEE cannot alone decide the value of COURSE_NO or STUD_NO;
COURSE_FEE together with STUD_NO cannot decide the value of COURSE_NO;
COURSE_FEE together with COURSE_NO cannot decide the value of STUD_NO;
Hence, COURSE_FEE would be a non-prime attribute, as it does not belong to the
one only candidate key {STUD_NO, COURSE_NO} ; But, COURSE_NO ->
COURSE_FEE, i.e., COURSE_FEE is dependent on COURSE_NO, which is a proper
subset of the candidate key. Non-prime attribute COURSE_FEE is dependent on a
proper subset of the candidate key, which is a partial dependency and so this
relation is not in 2NF. To convert the above relation to 2NF, we need to split the
table into two tables such as : Table 1: STUD_NO, COURSE_NO

Table 2: COURSE_NO, COURSE_FEE

Table 1Table 2
STUD_NO COURSE_NO COURSE_NO COURSE_FEE
1 C1 C1 1000
2 C2 C2 1500
1 C4 C3 1000
4 C3 C4 2000
4 C1 C5 2000
2 C5
Note – 2NF tries to reduce the redundant data getting stored in memory. For
instance, if there are 100 students taking C1 course, we dont need to store its Fee
as 1000 for all the 100 records, instead once we can store it in the second table as
the course fee for C1 is 1000.
Example-2: Consider following functional dependencies in relation R (A, B,
C, D )
AB -> C [A and B together determine C]
BC -> D [B and C together determine D]
Answer:
First, we can check if there are any partial dependencies. A partial dependency
occurs when a non-prime attribute (not part of any candidate key) depends on
only part of a candidate key.
The candidate keys for relation R can be determined by finding the closure of each
attribute:
AB determines every keys.
Now, let’s check for partial dependencies:
There are no partial dependencies in this relation because each non-prime
attribute (C and D) depends on the whole candidate key(s) it is part of (AB and BC,
respectively).
Therefore, the relation R is already in 3rd Normal Form (3NF) because it satisfies
the conditions of 1st Normal Form (1NF) and 2nd Normal Form (2NF) and does
not have any transitive dependencies

GROUP – C
ANS ANY 3
(a) Why are certain functional dependencies called trivial functional dependencies
What is Functional Dependency?
A functional dependency occurs when one attribute uniquely determines another
attribute within a relation. It is a constraint that describes how attributes in a table
relate to each other. If attribute A functionally determines attribute B we write
this as the A→B.
Functional dependencies are used to mathematically express relations among
database entities and are very important to understanding advanced concepts in
Relational Database Systems.
Example:

roll_no name dept_name dept_building

42 abc CO A4

43 pqr IT A3

44 xyz CO A4

45 xyz IT A3

46 mno EC B2

47 jkl ME B2

From the above table we can conclude some valid functional dependencies:
• roll_no → { name, dept_name, dept_building },→ Here, roll_no can
determine values of fields name, dept_name and dept_building, hence a
valid Functional dependency
• roll_no → dept_name , Since, roll_no can determine whole set of {name,
dept_name, dept_building}, it can determine its subset dept_name also.
• dept_name → dept_building , Dept_name can identify the dept_building
accurately, since departments with different dept_name will also have a
different dept_building
• More valid functional dependencies: roll_no → name, {roll_no, name} ⇢
{dept_name, dept_building}, etc.
Here are some invalid functional dependencies:
• name → dept_name Students with the same name can have different
dept_name, hence this is not a valid functional dependency.
• dept_building → dept_name There can be multiple departments in the
same building. Example, in the above table departments ME and EC are in
the same building B2, hence dept_building → dept_name is an invalid
functional dependency.
• More invalid functional dependencies: name → roll_no, {name, dept_name}
→ roll_no, dept_building → roll_no, etc.
Armstrong’s axioms/properties of functional dependencies:
1. Reflexivity: If Y is a subset of X, then X→Y holds by reflexivity rule
Example, {roll_no, name} → name is valid.
2. Augmentation: If X → Y is a valid dependency, then XZ → YZ is also valid by
the augmentation rule.
Example, {roll_no, name} → dept_building is valid, hence {roll_no, name,
dept_name} → {dept_building, dept_name} is also valid.
3. Transitivity: If X → Y and Y → Z are both valid dependencies, then X→Z is
also valid by the Transitivity rule.
Example, roll_no → dept_name & dept_name → dept_building, then
roll_no → dept_building is also valid.
Types of Functional Dependencies in DBMS
1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency
1. Trivial Functional Dependency
In Trivial Functional Dependency, a dependent is always a subset of the
determinant. i.e. If X → Y and Y is the subset of X, then it is called trivial
functional dependency
Example:

roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

Here, {roll_no, name} → name is a trivial functional dependency, since the

dependent name is a subset of determinant set {roll_no, name}. Similarly, roll_no
→ roll_no is also an example of trivial functional dependency.

(b) Use Armstrong's axioms to prove the soundness of the union rule.
The proof of Soundness –
Set of functional dependencies that can be derived with logic from a given set of
functional dependencies (Fclosure) and the set of functional dependencies that can
be inferred from Armstrong’s Axioms (Farmstrong) are same. In terms of sets, if we
want to show equality, then we mention that one is a proper subset of the other.
Hence, we need to prove that,

Farmstrong is a proper subset of Fclosure

for soundness.

This means that all things derived from F after applying Armstrong’s Axioms are
correct functional dependencies and this property is called Soundness.
Consideration for Soundness –
𝐺 ⟶ 𝐸 has been derived from F using Armstrong’s Axioms after m number of
steps.

In each step we would apply the rules of reflexivity, augmentation and transitivity.
(If we derive from Axioms which are only 3 in number, the repeated application of
them would also give similar results.)

Rule of Reflexivity –
This rule states that if E is a proper subset of G, then G implies E (𝐺 ⟶ 𝐸 ). This
rule always results in correct Functional Dependencies because it is a trivial
functional dependency.

Rule of Augmentation –
This rule states that for 𝐺 ⟶ 𝐸 , if we add Z on both sides, then we would
get 𝐺𝑍 ⟶ 𝑍𝐸 , which would be a correct functional dependency.
If ta and tb have the same corresponding values in G and Z (GZ),
It would mean that they have same corresponding value for G,
which would mean that they have same corresponding value for E, since G implies
E,
it would also mean that they correspond to E and Z (EZ).

Rule of Transitivity –
This rule states that if 𝐺 ⟶ 𝐸 , 𝐸 ⟶ 𝑍 then G determines Z (𝐺 ⟶ 𝑍 )
If ta and tb have the same corresponding values in G
which would mean that they have same corresponding value for E, since G implies
E holds,
it would also mean that they correspond to E, since E implies Z holds.

So, applying these 3 rules will always give rise to correct functional dependencies
(even if we apply them m number of times).
Hence proved.

(c) Compute the closure of the following set F of Ds for each relation schema
R = (A, B, C, D, E)
A -> BC
CD -> F
B -> D
E -> A
List the candidate key for R.
(a) Why is normalization necessary? Compare between BCNF and 3rd Normal
form.
The main reason for normalizing the relations is removing these anomalies.
Failure to eliminate anomalies leads to data redundancy and can cause data
integrity and other problems as the database grows. Normalization consists of a
series of guidelines that helps to guide you in creating a good database structure.
Data modification anomalies can be categorized into three types:
o Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a
new tuple into a relationship due to lack of data.
 o Deletion Anomaly: The delete anomaly refers to the situation where the
deletion of data results in the unintended loss of some other important
data.
o Updatation Anomaly: The update anomaly is when an update of a single
data value requires multiple rows of data to be updated.

Difference Between 3NF and BCNF

3NF BCNF

BCNF stands for Boyce Codd

3NF stands for Third Normal Form.
Normal Form.

In 3NF there should be no transitive

dependency that is no non prime attribute In BCNF for any relation A->B, A
should be transitively dependent on the should be a super key of relation.
candidate key.

It is comparatively more stronger

It is less stronger than BCNF.
than 3NF.

In BCNF the functional

In 3NF the functional dependencies are
dependencies are already in 1NF,
already in 1NF and 2NF.
2NF and 3NF.

The redundancy is comparatively

The redundancy is high in 3NF.
low in BCNF.

In BCNF there may or may not be

In 3NF there is preservation of all
preservation of all functional
functional dependencies.
dependencies.

It is comparatively easier to achieve. It is difficult to achieve.

 3NF BCNF

Lossless decomposition can be achieved Lossless decomposition is hard to

by 3NF. achieve in BCNF.

The table is in 3NF if it is in 2NF and for

each functional dependency X->Y at least The table is in BCNF if it is in 3rd
following condition hold: normal form and for each relation
(i) X is a super key, X->Y X should be super key.
(ii) Y is prime attribute of table.

3NF can be obtained without sacrificing all Dependencies may not be

dependencies. preserved in BCNF.

3NF can be achieved without losing any For obtaining BCNF we may lose
information from the old table. some information from old table.

(b) Consider the universal relation R={A, B, C, D, E, F, G, H, I, J} and the set of

functional dependencies f ={ AB → C, A → D, B → F, F → GH, D → IJ}.
What is the key for R? Normalize the relation R upto 3NF, justify your answer.
9. What is a Trigger in DBMS? What is a Stored Procedure in DBMS?Difference
between Trigger and Stored procedure in DBMS. What is a database cursor and
how to use it?
What is Triggers?
A trigger in DBMS is a set of instructions that are set ‘to trigger’ or execute
automatically in response to one or the other events taking place in the database.
Such events can be operations such as: INSERT, UPDATE or DELETE upon a specific
table or view. Triggers can also be specified to run before or after running the
trigger event depending on the desired action. It is usually applied for business
rules’ enforcement and for ensuring data consistency and updating tasks, which
should occur because of modification of data, in the organization.
Types of Triggers
• Before Triggers: These are performed before the operation is done or
before an operation such as INSERT, UPDATE OR DELETE is performed. They
can be used to check the values of input data and even transform this data
before it is inserted into or updated in the database.
• After Triggers: These are invoked after the operation is over. They are
effective in activities that involve capturing any modification or exercising
functions based on the event—firing or calling other events.
 • Instead of Triggers: These supplant the firing event, which in turn means
that the intended operation (INSERT, UPDATE, DELETE) does not occur, but
instead the action of the trigger is executed. These are usually applied with
views to compel specific kinds of logic.
Advantages of Triggers
• Protection of data
• Inhibits transactions that are not valid
• It also keeps the different tables in sync.
• Referential integrity is enforced with the use of triggers.
• Triggers can also be used in event logging and auditing.
Disadvantages of Trigger
• Complex Debugging: Debugging may be challenging because triggers run
automatically and users do not pin point how they are performing their
actions.
• Performance Overhead: Many triggers are bad for database performance,
particularly for high-transaction systems.
• Hidden Execution: This is because while the execution of triggers takes
place behind the scene, failure to document their execution may cause
some odd behaviors.
What is Procedures?
A procedure is a known as stored procedure, which is a sequence
of SQL statements which in case needed can be used more than once. One of
them is that compared to other forms of programming, procedures are called
directly by the user or the application for the purpose of executing some
particular operation. As a result of using them, it is easier to encapsulate logic for
performing operations in a database. Procedures, unlike triggers, can not be
initiated upon any event except when a user or an application program calls for
the procedure.
Advantages of Procedures
 • A Stored Procedure can be used as modular programming, which means
that it can be created once, stored, and called multiple times as needed.
This allows for speedier execution.
• Reduces network traffic
• Improving data security
• Easy to maintain because the stored procedure scripts are all in one place
and hence, it is easy to update and track dependencies when schema
changes occur.
• Testing can be carried out independent of the application.
Disadvantages of Procedures
• Manual Execution: It is essential to call procedures manually, which might
be annoying in cases where something needs to be done automatically.
• Less Flexibility in Event-Handling: They cannot be invoked as triggers do
whenever some event happens, and therefore are not very useful for tasks
that need constant updates.
Difference Between Triggers and Procedures

Parameters Triggers Procedures

A Trigger is implicitly A Procedure is explicitly called by

invoked whenever any the user/application using
event such as INSERT, statements or commands such as
DELETE, or UPDATE occurs exec, EXECUTE, or simply
Basics in a TABLE. procedure name

When an event occurs, a

A procedure helps to perform a
trigger helps to execute an
specified task when it is invoked.
Action action automatically.
Parameters Triggers Procedures

Only nesting of triggers can

be achieved in a table. We We can define/call procedures
cannot define/call a trigger inside another procedure.
Define/ call inside another trigger.

In a database, the syntax to In a database, the syntax to define

define a trigger: CREATE a procedure: CREATE PROCEDURE
Syntax TRIGGER TRIGGER_NAME PROCEDURE_NAME

Transaction statements
All transaction statements such
such as COMMIT,
as COMMIT and ROLLBACK are
Transaction ROLLBACK, and SAVEPOINT
allowed in procedures.
statements are not allowed in triggers.

Triggers are used to

maintain referential Procedures are used to perform
integrity by keeping a tasks defined or specified by the
record of activities users.
Usage performed on the table.

We cannot return values in

We can return 0 to n values.
a trigger. Also, as an input,
However, we can pass values as
we cannot pass values as a
parameters.
Return value parameter.
10. a) Name and briefly describe the five SQL built-in functions?
SQL Functions are built-in programs that are used to perform different operations
on the database.
There are two types of functions in SQL:
• Aggregate Functions
• Scalar Functions
SQL Aggregate Functions
SQL Aggregate Functions operate on a data group and return a singular output.
They are mostly used with the GROUP BY clause to summarize data.
Some common Aggregate functions with Syntax and description are shown in the
table below.

Aggregate
Function Description Syntax

SELECT AVG(column_name)
Calculates the average value
AVG() FROM table_name;

SELECT COUNT(column_name)
Counts the number of rows
COUNT() FROM table_name

Returns the first value in an SELECT FIRST(column_name)

FIRST() ordered set of values FROM table_name;

Returns the last value in an SELECT LAST(column_name)

LAST() ordered set of values FROM table_name;
Aggregate
Function Description Syntax

Retrieves the maximum SELECT MAX(column_name)

MAX() value from a column FROM table_name;

Retrieves the minimum SELECT MIN(column_name)

MIN() value from a column FROM table_name;

Calculates the total sum of SELECT SUM(column_name)

SUM() values in a numeric column FROM table_name;

SQL Scalar functions

SQL Scalar Functions are built-in functions that operate on a single value and
return a single value.
Scalar functions in SQL helps in efficient data manipulation and simplification of
complex calculations in SQL queries.

Scalar
function Description Syntax

Converts a string to SELECT UCASE(column_name) FROM

UCASE() uppercase table_name;

Converts a string to SELECT LCASE(column_name) FROM

LCASE() lowercase table_name;

Extracts a substring from SELECT MID(column_name, start,

MID() a string length) FROM table_name;
Scalar
function Description Syntax

Returns the length of a SELECT LEN(column_name) FROM

LEN() string table_name;

Rounds a number to a
SELECT ROUND(column_name,
specified number of
decimals) FROM table_name;
ROUND() decimals

Returns the current date

SELECT NOW();
NOW() and time

Formats a value with the SELECT FORMAT(column_name,

FORMAT() specified format format) FROM table_name;

b) Define and discuss data constraints?

Constraints are the rules that we can apply on the type of data in a table. That is,
we can specify the limit on the type of data that can be stored in a particular
column in a table using constraints.
The available constraints in SQL are:

• NOT NULL: This constraint tells that we cannot store a null value in a
column. That is, if a column is specified as NOT NULL then we will not be
able to store null in this particular column any more.
• UNIQUE: This constraint when specified with a column, tells that all the
values in the column must be unique. That is, the values in any row of a
column must not be repeated.
 • PRIMARY KEY: A primary key is a field which can uniquely identify each row
in a table. And this constraint is used to specify a field in a table as primary
key.
• FOREIGN KEY: A Foreign key is a field which can uniquely identify each row
in a another table. And this constraint is used to specify a field as Foreign
key.
• CHECK: This constraint helps to validate the values of a column to meet a
particular condition. That is, it helps to ensure that the value stored in a
column meets a specific condition.
• DEFAULT: This constraint specifies a default value for the column when no
value is specified by the user.

c) What is a cascading update and delete?

DELETE CASCADE: When we create a foreign key using this option, it deletes the
referencing rows in the child table when the referenced row is deleted in the
parent table which has a primary key.
UPDATE CASCADE: When we create a foreign key using UPDATE CASCADE the
referencing rows are updated in the child table when the referenced row is
updated in the parent table which has a primary key.
11. a) Draw the ERD of Online movie ticket booking system?
b) What are the fundamental operations in relational algebra?
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

1. Select Operation:
o The select operation selects tuples that satisfy a given predicate.
o It is denoted by sigma (σ).
1. Notation: σ p(r)
Where:
σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR
and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
For example: LOAN Relation

BRANCH_NAME LOAN_NO AMOUNT

Downtown L-17 1000

Redwood L-23 2000

Perryride L-15 1500

Downtown L-14 1500

Mianus L-13 500

Roundhill L-11 900

Perryride L-16 1300

Input:
1. σ BRANCH_NAME="perryride" (LOAN)
Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500

Perryride L-16 1300

2. Project Operation:
o This operation shows the list of those attributes that we wish to appear in
the result. Rest of the attributes are eliminated from the table.
o It is denoted by ∏.
1. Notation: ∏ A1, A2, An (r)
Where
A1, A2, A3 is used as an attribute name of relation r.
Example: CUSTOMER RELATION

NAME STREET CITY

Jones Main Harrison

Smith North Rye

Hays Main Harrison

Curry North Rye

Johnson Alma Brooklyn

Brooks Senator Brooklyn

Input:
1. ∏ NAME, CITY (CUSTOMER)
Output:

NAME CITY

Jones Harrison

Smith Rye

Hays Harrison

Curry Rye

Johnson Brooklyn

Brooks Brooklyn

3. Union Operation:
o Suppose there are two tuples R and S. The union operation contains all the
tuples that are either in R or S or both in R & S.
o It eliminates the duplicate tuples. It is denoted by ∪.
1. Notation: R ∪ S
A union operation must hold the following condition:
o R and S must have the attribute of the same number.
o Duplicate tuples are eliminated automatically.
Example:
DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO

Johnson A-101
 Smith A-121

Mayes A-321

Turner A-176

Johnson A-273

Jones A-472

Lindsay A-284

BORROW RELATION

CUSTOMER_NAME LOAN_NO

Jones L-17

Smith L-23

Hayes L-15

Jackson L-14

Curry L-93

Smith L-11

Williams L-17

Input:
1. ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:

CUSTOMER_NAME

Johnson

Smith

Hayes

Turner

Jones

Lindsay

Jackson

Curry

Williams

Mayes

4. Set Intersection:
o Suppose there are two tuples R and S. The set intersection operation
contains all tuples that are in both R & S.
o It is denoted by intersection ∩.
1. Notation: R ∩ S
Example: Using the above DEPOSITOR table and BORROW table
Input:
1. ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:

CUSTOMER_NAME

Smith

Jones

5. Set Difference:
o Suppose there are two tuples R and S. The set intersection operation
contains all tuples that are in R but not in S.
o It is denoted by intersection minus (-).
1. Notation: R - S
Example: Using the above DEPOSITOR table and BORROW table
Input:
1. ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)
Output:

CUSTOMER_NAME

Jackson

Hayes

Willians

Curry
6. Cartesian product
o The Cartesian product is used to combine each row in one table with each
row in the other table. It is also known as a cross product.
o It is denoted by X.
1. Notation: E X D
Example:
EMPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A

2 Harry C

3 John B

DEPARTMENT

DEPT_NO DEPT_NAME

A Marketing

B Sales

C Legal

Input:
1. EMPLOYEE X DEPARTMENT
Output:

EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME

1 Smith A A Marketing

1 Smith A B Sales

1 Smith A C Legal

2 Harry C A Marketing

2 Harry C B Sales

2 Harry C C Legal

3 John B A Marketing

3 John B B Sales

3 John B C Legal

7. Rename Operation:
The rename operation is used to rename the output relation. It is denoted
by rho (ρ).
Example: We can use the rename operator to rename STUDENT relation to
STUDENT1.
1. ρ(STUDENT1, STUDENT)
2.
C) Explain each operation with example. What are the differences between
DDL,DML,DCL.
What are SQL Commands?
SQL Commands are like instructions to a table. It is used to interact with the
database with some operations. It is also used to perform specific tasks, functions,
and queries of data. SQL can perform various tasks like creating a table, adding
data to tables, dropping the table, modifying the table, set permission for users.
SQL Commands are mainly categorized into five categories:
• DDL – Data Definition Language
• DQL – Data Query Language
• DML – Data Manipulation Language
• DCL – Data Control Language
• TCL – Transaction Control Language
1. Data Definition Language (DDL) in SQL
DDL or Data Definition Language actually consists of the SQL commands that can
be used to defining, altering, and deleting database structures such
as tables, indexes, and schemas. It simply deals with descriptions of the database
schema and is used to create and modify the structure of database objects in the
database
Common DDL Commands
Command Description Syntax

Create database or its

CREATE TABLE table_name
objects (table, index,
CREATE (column1 data_type, column2
function, views, store
data_type, ...);
procedure, and triggers)

Delete objects from the

DROP DROP TABLE table_name;
database

Alter the structure of the ALTER TABLE table_name ADD

ALTER
database COLUMN column_name data_type;

Remove all records from

a table, including all
TRUNCATE TRUNCATE TABLE table_name;
spaces allocated for the
records are removed

Add comments to the COMMENT 'comment_text' ON

COMMENT
data dictionary TABLE table_name;

Rename an object RENAME TABLE old_table_name

RENAME
existing in the database TO new_table_name;

Example of DDL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
 hire_date DATE
);
In this example, a new table called employees is created with columns for
employee ID, first name, last name, and hire date.
2. Data Query Language (DQL) in SQL
DQL statements are used for performing queries on the data within schema
objects. The purpose of the DQL Command is to get some schema relation based
on the query passed to it. This command allows getting the data out of the
database to perform operations with it. When a SELECT is fired against a table or
tables the result is compiled into a further temporary table, which is displayed or
perhaps received by the program.
DQL Command

Command Description Syntax

It is used to retrieve data SELECT column1, column2, ...FROM

SELECT
from the database table_name WHERE condition;

Example of DQL
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;
This query retrieves employees’ first and last names, along with their hire dates,
from the employees table, specifically for those in the ‘Sales’ department, sorted
by hire date.
3. Data Manipulation Language (DML) in SQL
The SQL commands that deal with the manipulation of data present in the
database belong to DML or Data Manipulation Language and this includes most of
the SQL statements. It is the component of the SQL statement that controls
access to data and to the database. Basically, DCL statements are grouped with
DML statements.
Common DML Commands

Command Description Syntax

Insert data into a INSERT INTO table_name (column1, column2,

INSERT
table ...) VALUES (value1, value2, ...);

Update existing
UPDATE table_name SET column1 = value1,
UPDATE data within a
column2 = value2 WHERE condition;
table

Delete records
DELETE from a database DELETE FROM table_name WHERE condition;
table

Table control
LOCK LOCK TABLE table_name IN lock_mode;
concurrency

Call a PL/SQL or
CALL CALL procedure_name(arguments);
JAVA subprogram

Describe the
EXPLAIN EXPLAIN PLAN FOR SELECT * FROM
access path to
PLAN table_name;
data

Example of DML
INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into the employees table with the first name
‘Jane’, last name ‘Smith’, and department ‘HR’.
4. Data Control Language (DCL) in SQL
DCL (Data Control Language) includes commands such
as GRANT and REVOKE which mainly deal with the rights, permissions, and other
controls of the database system. These commands are used to control access to
data in the database by granting or revoking permissions.
Common DCL Commands

Command Description Syntax

Assigns new privileges to

GRANT privilege_type
a user account, allowing
[(column_list)] ON [object_type]
GRANT access to specific
object_name TO user [WITH
database objects, actions,
GRANT OPTION];
or functions.

Removes previously
granted privileges from a REVOKE [GRANT OPTION FOR]
user account, taking away privilege_type [(column_list)] ON
REVOKE
their access to certain [object_type] object_name FROM
database objects or user [CASCADE];
actions.

Example of DCL
GRANT SELECT, UPDATE ON employees TO user_name;
This command grants the user user_name the permissions to select and update
records in the employees table.
5. Transaction Control Language (TCL) in SQL
Transactions group a set of tasks into a single execution unit. Each transaction
begins with a specific task and ends when all the tasks in the group are
successfully completed. If any of the tasks fail, the transaction fails. Therefore, a
transaction has only two results: success or failure. We can explore more
about transactions here.
Common TCL Commands

Command Description Syntax

BEGIN BEGIN TRANSACTION

Starts a new transaction
TRANSACTION [transaction_name];

Saves all changes made

COMMIT COMMIT;
during the transaction

Undoes all changes made

ROLLBACK ROLLBACK;
during the transaction

Creates a savepoint within

SAVEPOINT SAVEPOINT savepoint_name;
the current transaction

Example of TCL
BEGIN TRANSACTION;
UPDATE employees SET department = 'Marketing' WHERE department = 'Sales';
SAVEPOINT before_update;
UPDATE employees SET department = 'IT' WHERE department = 'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;
In this example, a transaction is started, changes are made, and a savepoint is set.
If needed, the transaction can be rolled back to the savepoint before being
committed.
Important SQL Commands
1. SELECT: Used to retrieve data from a database.
2. INSERT: Used to add new data to a database.
3. UPDATE: Used to modify existing data in a database.
4. DELETE: Used to remove data from a database.
5. CREATE TABLE: Used to create a new table in a database.
6. ALTER TABLE: Used to modify the structure of an existing table.
7. DROP TABLE: Used to delete an entire table from a database.
8. WHERE: Used to filter rows based on a specified condition.
9. ORDER BY: Used to sort the result set in ascending or descending order.
10.JOIN: Used to combine rows from two or more tables based on a related
column between them.
 Group-A 2023-2024
(Very Short Answer Type Question
1. Answer any ten of the following
1. During the ______ phase, the local variables that hold the write
operations are copied to the database

2. The dependency rules specified by the database designer are

known as ___

3. ____ is a predicate that we expect the database to always satisfy

4. What is the format of entering date into a database while

inserting data into it?

5. To select a tuple from a relational database table, the symbol used

in relational algebra is __

6. Which of the systems use RDMS?

7. What is the cardinality of a table of 50 rows and 5 columns?

8. Data about data is known as _______.

9. DML is provided for what purpose?

10.Grant and revoke are __________ statements

11.Which command cannot be used to modify the data in a

database?

12.A transaction for which all committed changes are permanent is

called _
 Group-B
(Short Answer Type Questio Answer any three of the following
2. Briefly explain different JOIN operation with example
What is Join?
Join is an operation in DBMS(Database Management System) that combines the
row of two or more tables based on related columns between them. The main
purpose of Join is to retrieve the data from multiple tables in other words Join is
used to perform multi-table queries. It is denoted by ⨝.
Syntax 1
R3 <- ⨝(R1) <join_condition> (R2)
where R1 and R2 are two relations to be joined and R3 is a relation that will hold
the result of the join operation.
Example
Temp <- ⨝(student) S.roll==E.roll(Exam)
where S and E are aliases of the student and exam respectively
SQL JOIN Example
Consider the two tables below as follows:

Table 1 - Student

Table 2 - StudentCourse
Both these tables are connected by one common key (column) i.e. ROLL_NO.
We can perform a JOIN operation using the given SQL query:
SELECT s.roll_no, s.name, s.address, s.phone, s.age, sc.course_id
FROM Student s
JOIN StudentCourse sc ON s.roll_no = sc.roll_no;
Output:

ROLL_NO NAME ADDRESS PHONE AGE COURSE_ID

1 HARSH DELHI xxxxxxxxxx 18 1

2 PRATIK BIHAR xxxxxxxxxx 19 2

3 PRIYANKA SILIGURI xxxxxxxxxx 20 2

4 DEEP RAMNAGAR xxxxxxxxxx 18 3

5 SAPTARHI KOLKATA xxxxxxxxxx 19 1

3. Explain the three-tier architecture. What is the difference between two-tier

architecture and three -tier architecture?
1. Two-Tier Database Architecture
In two-tier, the application logic is either buried inside the User Interface on the
client or within the database on the server (or both). With two-tier client/server
architectures, the user system interface is usually located in the user’s desktop
environment and the database management services are usually in a server that is
a more powerful machine that services many clients.
Two Tier Architecture
Three-Tier Database Architecture
In the three-tier, the application logic or process lives in the middle tier, it is
separated from the data and the user interface. Three-tier systems are more
scalable, robust and flexible. In addition, they can integrate data from multiple
sources. In the three-tier architecture, a middle tier was added between the user
system interface client environment and the database management server
environment. There are a variety of ways of implementing this middle tier, such as
transaction processing monitors, message servers, or application servers.

Three Tier Architecture

Difference Between Two-Tier And Three-Tier Database Architecture

Two-Tier Database Architecture Three-Tier Database Architecture

It is a Client-Server Architecture. It is a Web-based application.

In two-tier, the application logic is In three-tier, the application logic or

either buried inside the user interface process resides in the middle-tier, it is
on the client or within the database on separated from the data and the user
the server (or both). interface.

Two-tier architecture consists of two Three-tier architecture consists of

layers : Client Tier and Database (Data three layers : Client Layer, Business
Tier). Layer and Data Layer.

It is easy to build and maintain. It is complex to build and maintain.

Two-tier architecture runs slower. Three-tier architecture runs faster.

It is secured as client is not allowed

It is less secured as client can
to communicate with database
communicate with database directly.
directly.

It results in performance loss

It results in performance loss whenever the system is run on
whenever the users increase rapidly. Internet but gives more performance
than two-tier architecture.
Two-Tier Database Architecture Three-Tier Database Architecture

Example – Designing registration

Example – Contact Management
form which contains text box, label,
System created using MS-Access or
button or a large website on the
Railway Reservation System, etc.
Internet, etc.

4. Define Optimistic Concurrency Control with example

All data items are updated at the end of the transaction, at the end, if any data
item is found inconsistent with respect to the value in, then the transaction is
rolled back.
Check for conflicts at the end of the transaction. No checking while the
transaction is executing. Checks are all made at once, so low transaction execution
overhead. Updates are not applied until end-transaction. They are applied to local
copies in a transaction space.
Phases
The optimistic concurrency control has three phases, which are explained below −
Read Phase
Various data items are read and stored in temporary variables (local copies). All
operations are performed in these variables without updating the database.

Validation Phase
All concurrent data items are checked to ensure serializability will not be validated
if the transaction updates are actually applied to the database. Any changes in the
value cause the transaction rollback. The transaction timestamps are used and the
write-sets and read-sets are maintained.
To check that transaction A does not interfere with transaction B the following
must hold −
 • TransB completes its write phase before TransA starts the read phase.
• TransA starts its write phase after TransB completes its write phase, and the
read set of TransA has no items in common with the write set of TransB.
• Both the read set and write set of TransA have no items in common with
the write set of TransB and TransB completes its read before TransA
completes its read Phase.
Write Phase
The transaction updates applied to the database if the validation is successful.
Otherwise, updates are discarded and transactions are aborted and restarted. It
does not use any locks hence deadlock free, however starvation problems of data
items may occur.
Problem
S: W1(X), r2(Y), r1(Y), r2(X).
T1 -3
T2 – 4
Check whether timestamp ordering protocols allow schedule S.
Solution
Initially for a data-item X, RTS(X)=0, WTS(X)=0
Initially for a data-item Y, RTS(Y)=0, WTS(Y)=0

For W1(X) : TS(Ti)<RTS(X) i.e.

TS(T1)<RTS(X)
TS(T1)<WTS(X)
3<0 (FALSE)
=>goto else and perform write operation w1(X) and WTS(X)=3
For r2(Y): TS(T2)<WTS(Y)
4<0 (FALSE)
=>goto else and perform read operation r2(Y) and RTS(Y)=4
For r1(Y) :TS(T1)<WTS(Y)
3<0 (FALSE)
=>goto else and perform read operation r1(Y).
For r2(X) : TS(T2)<WTS(X)
4<3 (FALSE)
=>goto else and perform read operation r2(X) and RTS(X)=4

5. Define Entity. Explain Strong and Weak Entity Set

An entity is a “thing” or “object” in the real world. An entity contains attributes,
which describe that entity. So anything about which we store information is called
an entity. Entities are recorded in the database and must be distinguishable, i.e.,
easily recognized from the group. In this article, we will see the difference
between strong and weak entities.
Difference Between Strong and Weak Entity

Strong Entity Weak Entity

Strong entity always has While a weak entity has a partial

a primary key. discriminator key.

Strong entity is not dependent

Weak entity depends on strong entity.
on any other entity.

Strong entity is represented by a Weak entity is represented by a double

single rectangle. rectangle.

While the relation between one strong and

Two strong entity’s relationship is
one weak entity is represented by a double
represented by a single diamond.
diamond.

Strong entities have either total

A weak entity has a total participation
participation or partial
constraint.
participation.

6. What is primary indexing and secondary indexing?

A Primary Index is an ordered file whose records are of fixed length with two
fields. The first field of the index is the primary key of the data file in an ordered
manner, and the second field of the ordered file contains a block pointer that
points to the data block where a record containing the key is available.
Primary Indexing
Working of Primary Indexing
• In primary indexing, the data file is sorted or clustered based on the
primary key as shown in the below figure.
• An index file (also known as the index table) is created alongside the data
file.
• The index file contains pairs of primary key values and pointers to the
corresponding data records.
• Each entry in the index file corresponds to a block or page in the data file.
Secondary indexing is a database management technique used to create
additional indexes on data stored in a database. The main purpose of secondary
indexing is to improve the performance of queries and to simplify the search for
specific records within a database. A secondary index provides an alternate means
of accessing data in a database, in addition to the primary index. The primary
index is typically created when the database is created and is used as the primary
means of accessing data in the database. Secondary indexes, on the other hand,
can be created and dropped at any time, allowing for greater flexibility in
managing the database.
 Group-C
Answer any three of the following
7. How does a B-tree differ from a B+ tree ? Outline an algorithm for insertion of a
record in a B+ t Construct a B+ tree for the following set of key values under the
assumption that the number of key values that fit in a node is 3. Key values are ( 3,
10, 12, 14, 29, 38, 45, 55, 60, 68 ). Use your algorithm to show the steps involved
for inserting 11 and 30

Basis of
Comparison B tree B+ tree

All internal and leaf nodes have Only leaf nodes have data
Pointers
data pointers pointers

All keys are at leaf nodes,

Since all keys are not available at
Search hence search is faster and
leaf, search often takes more time.
more accurate.

Duplicate of keys are

Redundant No duplicate of keys is maintained
maintained and all nodes
Keys in the tree.
are present at the leaf.

Insertion is easier and the

Insertion takes more time and it is
Insertion results are always the
not predictable sometimes.
same.
Basis of
Comparison B tree B+ tree

Deletion of the internal node is Deletion of any node is

Deletion very complex and the tree has to easy because all node are
undergo a lot of transformations. found at leaf.

Leaf nodes are not stored as Leaf nodes are stored as

Leaf Nodes
structural linked list. structural linked list.

Sequential access is
Sequential access to nodes is not
Access possible just like linked
possible
list

Height is lesser than B

For a particular number nodes
Height tree for the same number
height is larger
of nodes

B+ Trees used in
B-Trees used in Databases, Search
Application Multilevel Indexing,
engines
Database indexing

Each intermediary node

Number of Number of nodes at any
can have n/2 to n
Nodes intermediary level ‘l’ is 2l.
children.

TREE KHUD BANA LENA NAHI MILAA

8. Consider the relational database, where the primary keys are underline
Employee(ename,street,city)
Works(ename,company_name,salary)
Company(company_name,city)
Manages(ename,manager_name)\
Write the query expressions for each of the following queries in special language:
a. Find the names of all employees who work for the First Bank Corporation.
b. Find the names of all employees who live in the same city and on the same
street as do their managers.
c.Display the employee name,employee number for all employees who earn more
than average salary.
d. Display the employee number, employee name for all employees who work in a
department with any employee whose name contains a 'T'.
9. What is the concept of sub-query in terms of SQL?
What is the use of DROP command and what are the differences between DROP,
TRUNCATE and DELE commands?
What is Correlated Subquery in DBMS?What is the main difference between
UNION a UNION ALL?
10. What is the difference between DBMS and RDBMS?
What is Entity Integrity and Referential Integrity?
11. Why ER model is used? Benefits of ER modeling.
Entity is a thing or an object in real world. As the name suggests that Entity
Relationship model uses collection of basic objects called entities &
relationships.It develops a very simple and easy to design view of data. Entity
relationship model is widely used in Database design. To get some clarity about
an ER-Model look at the following example:
From the above example we can see the relationship WORK_FOR is between
entities EMPLOYEE and DEPARTMENT. It is a sample diagram as the original
diagram includes many entities and relationships.
Advantages
1. SIMPLE : It is simple to draw an ER diagram when we know entities and
relationships.
2. EFFECTIVE : It is an effective communication tool.
3. EASY TO UNDERSTAND : The design of ER is very logical and hence they are
easy to design and understand.They show database capabilities like how
tables, keys and columns are used to find a solution to the given question.
4. INTEGRATED : The ER Model can be easily integrated with relational model.
5. USEFUL IN DECISION MAKING : Since some of the entities in the database
are analyzed by an ER-Diagram, So by drawing an ER-Diagram we come to
know what kind of attributes and relationship exist between them.
6. EASY CONVERTION : It can be easily converted to other type of models.
7. DATABASE TROUBLESHOOTING : ER diagrams are used to analyze existing
databases to find and resolve the key issues in logic or deployment. Drawing
the diagram should reveal where it’s going wrong.
8. FLEXIBLE: ER models are flexible and can be modified easily to
accommodate changes in the database design or structure.
 9. MINIMIZES DATA REDUNDANCY: ER models help minimize data redundancy
by identifying and eliminating duplicate data entries.
10.SCALABILITY: ER models can be used for databases of varying sizes, from
small to large.
11.VISUAL REPRESENTATION: ER models provide a visual representation of the
database structure, making it easier for stakeholders to understand and
provide feedback.
12.DOCUMENTATION: ER models provide useful documentation for future
reference, making it easier to maintain and update the database in the long
run.
Disadvantages
1. LOSS OF INFORMATION: While drawing an ER Model some of the
information can be hidden or lost.
2. LIMITED RELATIONSHIP: ER model can represent limited relationships as
compared to other models and It is not possible to indicate primary keys
and foreign keys when they’re expected.
3. NO REPRESENTATION FOR DATA MANIPULATION: It is not possible to
represent data manipulation(commands like
insert(),delete(),alter(),update()) in ER model.
4. NO INDUSTRY STANDARD: There is no industry standard for notations of an
ER diagram.
5. DATA INCONSISTENCY: Due to improper Normalization some data
inconsistency may occur so, while creating an ER diagram at least it should
be in third normal form.
6. MISSING CARDINALITIES: Missing relationship cardinalities, so everything
looks like a one-to-one relationship. One-to-one relationships are actually
quite rare.
7. DIFFICULT TO MODIFY: ER models can be difficult to modify once they are
created. Any changes made to the model may require extensive rework,
which can be time-consuming and expensive.
 8. LIMITED ATTRIBUTE REPRESENTATION: ER models may not be able to
represent all the attributes required for a particular problem domain. This
can lead to either the loss of important data or the creation of a complex
and unwieldy model.
9. LACK OF SUPPORT FOR BUSINESS RULES: ER models do not provide
support for business rules, which can make it difficult to ensure data
integrity and enforce constraints.
10.DIFFICULT TO SCALE: ER models can be difficult to scale to accommodate
larger data sets. As the amount of data in the database grows, the ER model
may need to be modified to handle the increased complexity.
11.DEPENDENCE ON USER UNDERSTANDING: ER models rely heavily on the
understanding of the user to correctly identify the entities and
relationships. If the user does not have a good understanding of the
problem domain, the resulting ER model may not accurately represent the
data.
12.LIMITED SUPPORT FOR ABSTRACTION: ER models are not designed to
support abstraction, which can make it difficult to represent complex
relationships or data structures in a simple and intuitive way.

Find the minimum number of tables required to represent the given ER diagram in
relational model.
Applying the rules that we have learnt, minimum 6 tables will be required-

• Account (Ac_no, Balance, b_name)

• Branch (b_name, b_city, Assets)
• Loan (L_no, Amt, b_name)
• Borrower (C_name , L_no)
• Customer (C_name, C_street, C_city)
• Depositor (C_name, Ac_no)