M1:

https://www.geeksforgeeks.org/relational-model-in-dbms/

What is a database management system (DBMS)? Write Advantages of Database

Management System.

Ans: A database-management system (DBMS) is a collection of interrelated data and a set of

programs to access those data. The collection of data, usually referred to as the database, The
primary goal of a DBMS is to provide a way to store and retrieve database information that is
both convenient and efficient.

It’s allows users to create, manage, and interact with databases, enabling them to store,
retrieve, update, and delete data while ensuring data integrity, security, and accessibility.

Key Functions:
• Data Storage and Retrieval: DBMS provides efficient mechanisms for storing and
retrieving data from the database.
• Data Definition: It allows users to define the structure of the database, including
tables, columns, data types, and relationships.
• Data Manipulation: DBMS enables users to insert, update, delete, and query data
within the database.
• Data Security: It provides mechanisms for controlling access to the database and
protecting sensitive data.
 • Data Integrity: DBMS ensures that the data in the database is accurate, consistent,
and reliable.

Advantages of DBMS
1. Data organization: A DBMS allows for the organization and storage of data in a
structured manner, making it easy to retrieve and query the data as needed.
2. Data integrity: A DBMS provides mechanisms for enforcing data integrity
constraints, such as constraints on the values of data and access controls that restrict
who can access the data.
3. Concurrent access: A DBMS provides mechanisms for controlling concurrent access
to the database, to ensure that multiple users can access the data without conflicting
with each other.
4. Data security: A DBMS provides tools for managing the security of the data, such as
controlling access to the data and encrypting sensitive data.
5. Backup and recovery: A DBMS provides mechanisms for backing up and
recovering the data in the event of a system failure.
6. Data sharing: A DBMS allows multiple users to access and share the same data,
which can be useful in a collaborative work environment.

Types of DBMS:
• Relational DBMS (RDBMS): Organizes data into tables with rows and columns,
using SQL (Structured Query Language) for querying and manipulating
data. Examples include MySQL, Oracle, and PostgreSQL.
• NoSQL DBMS: Designed to handle large volumes of unstructured or semi-structured
data, often prioritizing scalability and flexibility over strict data
consistency. Examples include MongoDB and Cassandra.
• Other types: Hierarchical, network, and object-oriented DBMS are also available,
each with its own data organization and query language.

Data Abstraction

M2:

Define E-R diagram and how does it help in system development.

Definition of E-R Diagram

An Entity-Relationship (E-R) Diagram is a graphical representation of a database that

illustrates how entities (objects) are related to each other. It is used in database design to
model real-world relationships between data.

It consists of:

• Entities (represented as rectangles) – objects in the system (e.g., Student, Employee).

• Attributes (represented as ovals) – properties of entities (e.g., Name, Age).
 • Relationships (represented as diamonds) – connections between entities (e.g.,
Enrolled in, Works for).

How E-R Diagram Helps in System Development

1. Clear Understanding of System Requirements

o Provides a visual representation of how data is structured and related.
o Helps in analyzing system requirements effectively.
2. Efficient Database Design
o Ensures normalization and removes redundancy in the database.
o Helps in designing relationships between tables properly.
3. Better Communication
o Acts as a blueprint for developers, designers, and stakeholders.
o Improves collaboration between technical and non-technical teams.
4. Reduces Complexity
o Breaks down complex business processes into simpler entities and
relationships.
o Helps in error detection before implementation.
5. Foundation for Relational Databases
o Serves as a base for relational schema conversion.
o Guides in creating tables, foreign keys, and constraints.

Thus, an E-R diagram is a crucial first step in designing efficient and well-structured
databases for any system.
Extended E-R Features

Although the basic E-R concepts can model most database features, some aspects of a database may
be more aptly expressed by certain extensions to the basic E-R model.

Using the ER model for bigger data creates a lot of complexity while designing a database model, So
in order to minimize the complexity Generalization, Specialization, and Aggregation were introduced
in the ER model.

M3: Relational Algebra

Relational Query Languages

https://www.geeksforgeeks.org/relational-query-language-in-dbms/

• A query language is a language in which a user requests information from the database.

• These languages are usually on a level higher than that of a standard programming language.

Types of Relational Query Language

There are two types of relational query language:
• Procedural Query Language
• Non-Procedural Language

Procedural Query Language

In Procedural Language, the user instructs the system to perform a series of operations on the
database to produce the desired results. Users tell what data to be retrieved from the database and
how to retrieve it. Procedural Query Language performs a set of queries instructing the DBMS to
perform various transactions in sequence to meet user requests.

Non-Procedural Language
In Non Procedural Language user outlines the desired information without giving a specific
procedure or without telling the steps by step process for attaining the information. It only gives a
single Query on one or more tables to get .The user tells what is to be retrieved from the database
but does not tell how to accomplish it.

➢ In nonprocedural language, the user describes the desired information without giving a
specific procedure for obtaining that information.

➢ Relational Algebra (Procedural) and Relational Calculus (non-procedural) are

mathematical system/ query languages which are used for query on relational model.

RA and RC are not executed in any computer they provide the fundamental mathematics on
which SQL is based.

SQL (structured query language) works on RDBMS, and it includes elements of both procedural
or non-procedural query language.
 Relational model RDBMS
RA, RC SQL
Algo Code
Conceptual Reality
Theoretical Practical
Chess Battle Field

• RA and RC are not executed in any computer they provide the fundamental mathematics on
which SQL is based.

The Relational Algebra

https://www.geeksforgeeks.org/introduction-of-relational-algebra-in-dbms/

• Relational algebra refers to a procedural query language that takes relation instances as input
and returns relation instances as output. It’s used to query and manipulate relational
databases, consisting of a set of operations like selection, projection, union, and join. It
provides a mathematical framework for querying databases, ensuring efficient data retrieval
and manipulation. Relational algebra serves as the mathematical foundation for query SQL.

• It performs queries with the help of operators.

• A binary or unary operator can be used.

• Some of these operations, such as the select, project, and rename operations, are called unary
operations because they operate on one relation.

• Cartesian product, and set difference, operate on pairs of relations and are, therefore, called
binary operations.

• It is also a conceptual language. By this we mean that the queries made in it are not run on the
computer so it is not used as a business language. However, this knowledge allows us to
understand the optimization and query execution of RDBMS.

BASIC / FUNDAMENTAL OPERATORS

• The fundamental operations in the relational algebra are select, project, union, set
difference, Cartesian product, and Rename.

DERIVED OPERATORS
➢ There are several other operations namely: set intersection, natural join, and
assignment.

Relational Schema: https://www.geeksforgeeks.org/relation-schema-in-dbms/

• The relation schema defines the structure of data stored in a database, specifying attributes
and relationships.
Select operation

• Selection Operation is basically used to filter out rows from a given table based on certain given
condition. It basically allows you to retrieve only those rows that match the condition as per
condition passed.
• The select operation selects tuples that satisfy a given predicate.
• We use the lowercase Greek letter sigma (σ) to denote selection.
• The predicate appears as a subscript to σ.
• The argument relation is in parentheses after the σ.

• 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 =, ≠, ≥, <, >, ≤.

We can find all instructors with salary greater than $90,000 by writing:

σ
dept name =“Physics” ∧ salary>90000 (instructor)

σ
dept name =building(department)

• Thus, to select those tuples of the instructor relation where the instructor is in the “Physics”
department, we write:

σ
dept name =“Physics” (instructor)
σ
BRANCH_NAME="perryride" (LOAN)

• It is a unary operation because works only a single relation.

• It operates on each row of the relation independently.

Projection operation (π)

• While Selection operation works on rows, similarly projection operation of relational algebra
works on columns. It basically allows you to pick specific columns from a given relational table
based on the given condition and ignoring all the other remaining columns.
Union operation (U):
➢ Union Operator is basically used to combine the results of two queries into a single result.

➢ The only condition is that both queries must return same number of columns with
same data types. Union operation in relational algebra is the same as union operation in set
theory.

➢ Written as, Expression1∪Expression2, r ∪s = {t | t ∈r or t ∈s}

➢ For a union operation r ∪s to be valid,

o we require that two conditions hold: The relations r and s must be of the same arity.
That is, they must have the same number of attributes.
o The domains of the ith attribute of r and the ith attribute of s must be the same, for
all i.

Questions:

M1:

1. State the 3 schema’s of data abstraction in DBMS. (2023).

2. What is a database management system (DBMS)? Write Advantages of Database Management
System (DBMS). (2023).
3. Describe the three-schema architecture in DBMS. (2019).
4. Draw a schematic diagram of DBMS. (2019)
5. Explain the ACID properties for a transaction. (2017).
6. Write short notes on any three of the following: Database approach and the file based approach.
(2017).

M2:
1. Define E-R diagram and how does it help in system development.
2. Define super key, candidate key and primary key (2019).
3. How to represent a weak entity set in ER diagram? Quote suitable example. What is NULL?
What is its importance? (2019).
4. Design an. ER-diagram for traditional "Library Management System". (2019).
5. Explain the difference between external, internal and conceptual schemas. Distinguish
between logical and physical data independence. (2018).
6. "All super keys are not candidate keys but the vice-versa is true". - Justify the statement.
(2018).
7. What is meant by spurious tuple? (2018).
8. With proper diagram explain extended ER features (Generalization, Specialization and
Aggregation). (2018).
9. Draw a sample ER diagram for a college administration system. It should keep information
like name and contact number of employees and students, attendants, salary statement of
employees, department wise class and room allotment and examination result of students.
(2018).
10. Write the algorithm to find out the candidate key from given a relation. (2018).
11. Explain the different levels of abstraction of the data-base management system. (2017).
12. What is constraint? Explain domain constraint and Entity Integrity constraint. (2017).
13. What is Relationship? Explain different degrees of relationship. (2017).
14. "All primary keys are-the superkeys but converse is not true." - Clarify. Define _multi-valued
attribute and composite attribute with suitable example. (2017).
15. Draw an E-R Diagram for a library management system. (2017).
16. Explain the following terms with example: Aggregation, Specialization, Generalization,
Derived Attribute, Unary Relationship. (2017).
17. Write short notes on any three of the following: Strong entity and weak entity. (2017).
M3:
1. Express division operation in terms of basic relational algebra operations.
2. Write short notes on any three of the following: (c) Selection and Projection.
M4:
1. Consider the following schema: Employee_Salaty (EmpNo, EName, Dept, DOB, Salary)Write
SQL to perform the following:
• Display the number of employees In each department.
• Display the total and average salaries of employees in "Computer Science"
department.
• Display the sum of salaries for all departments ..
• Display the highest and lowest salary for "Computer Science" department.
• Display the names of those employees whose name starts with "A". (2019).
2. Consider the relation: Bank (customer_name, accountrio, account type, balance and branch),
• Retrieves the name of the customer' who has an account in 'Dunlop' branch and
balance less than 10,000. –
• Lists the information of all the customers of saving branch.
• Displays the balance of those customers whose name starts with 'A'.
• Retrieves the total balance amount for individual branch.
• Who have the minimum balance among all the customers? (2018)
3. Consider the following two schemas:
EMPLOYEE (EMP.ro, FNAME, ADDRESS, JOIN_DATE, SALARY, MANAGER#, DEPT_ID).
DEPT(DEPT_ID, DNAME, LOCATION).
Write appropriate SQL statements based on above tables.
(a) List the details of employees whose salary is less than the average salary.
(b) List the department id and the number of employees working in that department.
(c) List the name of employees whose name have exactly five letters.
(d) List the details of employees who are more than 10 years old in the company.
(e) Display the minimum and maximum salary of the employee. (2018).
4. Consider the following two schemas :
EMPLOYEE (EMP#, ENAME, JOB, HIREDATE,
MANAGER#,SALARY,COMM, DEPT#).
DEPARTMENT(DEPT#, DNAME, LOCATION)
Perform the following queries on the tables (Write appropriate SQL statement):
i) List the name, salary and PF amounts of all employees (PF is calculated at 10% of the
basic).
ii) List the number of employees and average salary in DEPT#20.
iii) List the department number and total salary payable in each department.
iv) List the names of the employees who are more than 20 years old in the company.
v) List the names of the employee whose name either starts or ends with'S'.