DBMS PYQ 2020

1. Explain the concept of en ty integrity.

-> En ty integrity ensures that every table has a primary key, and that the primary key values are unique
and not null. This guarantees that each row in a table is uniquely iden fiable, which is essen al for
maintaining data accuracy and consistency.

2. What do you mean by func onal dependency?

-> Func onal dependency is a rela onship between two a ributes in a database. If a ribute A uniquely
determines a ribute B, then B is said to be func onally dependent on A. This is denoted as A → B. It helps
in iden fying rela onships and normalizing databases.

3. Whether rela onal calculus is procedural or non-procedural?

-> Rela onal calculus is non-procedural. It specifies what data is required rather than how to retrieve it.
Users describe their requirements in terms of condi ons, and the system determines the procedure to
fetch the data.

4. Define data dic onary and explain metadata.

-> A data dic onary is a centralized repository that stores informa on about database objects such as
tables, columns, data types, constraints, and rela onships. Metadata refers to the data about data,
such as the structure, format, and constraints of the data stored in the database.

5. What is alternate key?

-> An alternate key is a candidate key that is not chosen as the primary key. It can uniquely iden fy
records in a table but serves as an alterna ve to the primary key for iden fica on.
Example:
Consider a table Employee with the following a ributes:
 EmployeeID (Primary Key)
 Email (Unique)
 PhoneNumber (Unique)
Here, both Email and PhoneNumber are candidate keys. If EmployeeID is chosen as the primary
key, Email and PhoneNumber become alternate keys.

6. State the problems caused by data redundancy.

-> Data redundancy leads to issues like:
 Increased storage costs.
 Data inconsistency when the same data is updated in one place but not in others.
 Difficulty in maintaining and upda ng data.
 Higher risk of anomalies during data manipula on.

7. What is the requirement of specializa on in the ER data model?

-> Specializa on in the ER model is required to represent subclasses or subsets of an en ty that have
addi onal a ributes or rela onships specific to them. It allows for more detailed and organized data
representa on.

8. What do you understand by transi vity rule of func onal dependency?

-> The transi vity rule states that if A → B and B → C, then A → C. It helps iden fy indirect
dependencies, which is essen al for normalizing a database.
Example:
In a Student table:
 StudentID → DepartmentID (Each student belongs to a department).
 DepartmentID → DepartmentName (Each department has a unique name).
By transi vity:
StudentID → DepartmentName.
 DBMS PYQ 2020
This means the StudentID indirectly determines the DepartmentName.

9. (a) Describe the three-level architecture of DBMS. (5)

-> The three-level architecture of a Database Management System (DBMS) is designed to separate the
user’s view of the data from the physical representa on. The levels are:
1. External Level (User View):
 This is the highest level and defines how individual users view the data.
 It consists of mul ple user views (schemas) tailored to specific user needs.
 For example, a sales manager may see customer sales data, while an HR manager
sees employee records.
2. Conceptual Level (Logical View):
 This level represents the en re database structure logically.
 It hides physical details and focuses on what data is stored and rela onships
between them.
 All external views are derived from this level.
3. Internal Level (Physical View):
 This is the lowest level and describes how data is physically stored on hardware.
 It deals with file organiza on, indexing, and storage techniques.
Advantages of Three-Level Architecture:
 Provides data abstrac on.
 Ensures data independence (logical and physical).
 Supports mul ple views of the same data.
 DBMS PYQ 2020
(b) Explain the difference between physical and logical data independence. (3)
->
Factors Physical Data Independence Logical Data Independence
Basics Physical data independence mainly deals with Logical data independence mainly deals with
how data is stored in a system. the definition and structure of data in a
system.
Retrieval Simplicity It is relatively easy to retrieve. Retrieving is more difficult because the data
depends more on its logical structure than its
physical location.
Ease of Physical data independence is easier to Achieving logical data independence is harder
Implementation achieve compared to logical data compared to physical data independence.
independence.
Degree of Changes Changes at the physical level do not need to Changes at the logical level need to be
Required be reflected at the application level. reflected at the application level.
Internal Internal modifications may be required to Modifications at the logical level are necessary
Modification enhance system performance. regardless of whether the database structure
needs to be changed.
Type of Schema The focus is primarily on the internal schema. The focus is primarily on the conceptual
schema.
Examples For example, changing data compression For example, adding, removing, or modifying
methods, switching storage devices, and an attribute in a system.
modifying hashing algorithms.

(c) State the differences between schema and instance. (2)

->
 DBMS PYQ 2020
10. (a)What do you understand by the term closure of a rela on (r) with func onal
dependency set (F)? (2)
-> The closure of a rela on (r) with a func onal dependency set (F) refers to the set of all possible
a ributes that can be func onally determined from a given set of a ributes in the rela on, using the
func onal dependencies in F. It helps in finding candidate keys and ensuring the normaliza on of
databases.

(b) Compute the closure for rela on r {l, m, n, o, p} with func onal dependency set F
as given below:
F = {l  mn; no  p; m  o; p  l}
Iden fy the candidate key for the rela on (r). (5+3)

11. (a) Compare between 3NF and BCNF with example. (3)
 DBMS PYQ 2020
(b) Discuss the ‘inser on anomalies’, ‘upda on anomalies’ and ‘dele on anomalies’
with respect to normal forms with suitable examples and suggest a method to
overcome them. (5)
-> Inser on Anomaly:
 These anomalies occur when it is not possible to insert data into a database because the
required fields are missing or because the data is incomplete.
 Example: In a table storing Students and their Courses, if a student hasn't enrolled in any
course, inser ng a new student record without course informa on isn't possible.
 Solu on: Normalize the database to 3NF or BCNF to separate en es like Students and
Courses into different tables.
Upda on Anomaly:
 These anomalies occur when modifying data in a database and can result in inconsistencies
or errors.
 Example: If a Department name changes in a table storing Employees with Department, all
rows for that department need upda ng.
 Solu on: Normalize the database, placing departments in a separate table linked by a
foreign key.
Dele on Anomaly:
 These anomalies occur when dele ng a record from a database and can result in the
uninten onal loss of data.
 Example: Dele ng the last Course for a student might remove all informa on about the
student.
 Solu on: Normalize the database to 3NF or higher to ensure that dele ng a record doesn’t
cause unintended data loss.

(c) What is lossless decomposi on? (2)

-> Lossless decomposi on is a property of database normaliza on where a rela on is decomposed
into two or more smaller rela ons without losing any data. This means that when the decomposed
rela ons are joined back, they should result in the original rela on. It ensures that no informa on is
lost during decomposi on and that the original rela on can be perfectly reconstructed from the
smaller rela ons.

12. (a) Explain full outer join, le outer join, right outer join with example. (5)
-> Full Outer Join:
 Combines rows from both tables, returning all matching rows and all unmatched rows from
both tables, filling with NULL where there is no match.
 Example:
Table A: Table B:
ID Name ID Course
1 Alice 2 Math
2 Bob 3 Science

Result of Full Outer Join:

ID Name Course
1 Alice NULL
2 Bob Math
3 NULL Science
Le Outer Join:
 Returns all rows from the le table and the matched rows from the right table. Unmatched
rows from the right table are filled with NULL.
 DBMS PYQ 2020
 Example:
Table A: Table B:
ID Name ID Course
1 Alice 2 Math
2 Bob 3 Science

Result of Le Outer Join:

ID Name Course
1 Alice NULL
2 Bob Math
Right Outer Join:
 Returns all rows from the right table and the matched rows from the le table. Unmatched
rows from the le table are filled with NULL.
 Example:
Table A: Table B:
ID Name ID Course
1 Alice 2 Math
2 Bob 3 Science

Result of Full Outer Join:

ID Name Course
2 Bob Math
3 NULL Science

(b) Consider the rela on schemas:

STUDENT (student_id, name)
ENROLLEDIN (student_id, subject_code)
SUBJECTS (subject_code, teacher)
Write rela onal algebra for the following: (5)
(i) Who teaches CP 1500 or CP 3020?
(ii) What are the names of the students taking a subject taught by Roger?
->

13. Consider following two rela on schemas:

Employee (eno, ename, job, hiredate, managerno, salary, comm., dno)
Dept (dno, dname, loca on)
Solve the following queries using SQL:
(a) List the name of the employee whose name either starts or ends with “S”. (3)
 DBMS PYQ 2020
(b) List the department name and the total salary payable in each department. (3)
(c) List out the employees who earn more than the average salary of their
department. (4)
-> (a) SELECT ename
FROM employee
WHERE ename LIKE "S%" OR ename LIKE "%S";

(b) SELECT d.dname as Department, sum(e.salary) as total_salary

FROM dept d
JOIN employee e ON d.dno = e.dno
GROUP BY d.dname;

(c) SELECT e.eno AS Employee_No,

e.ename AS Employee_Name,
e.salary AS Salary,
d.dname AS Department_Name
FROM employee e
JOIN dept d ON d.dno = e.dno
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employee e2
WHERE e2.dno = e.dno);

14. Suppose schema R = (A, B, C, D, E) with F = (A  BC, CD  E, B  D, E  A) is

decomposed into (A, B, C) and (A, D, E). Show that this decomposi on is lossless-join
decomposi on. (5)
-> Lossless-Join Condi on
The decomposi on of R into R1 and R2 is lossless if the intersec on of R1 and R2 forms a superkey of at
least one of the rela ons.
1. Intersec on:
o R1 ∩ R2= {A}.
2. Key Requirement:
o The a ribute A (intersec on) must be a superkey in either R1R_1R1 or R2R_2R2.
1. Closure of A in R1 (A, B, C):
 A is present in R1.
 Using A→ BC:
o A+= {A, B, C} in R1.
 Since A+ includes all a ributes of R1 , A is a superkey for R1.
2. Closure of A in R2 (A, D, E):
 A is present in R2.
 Using E → AE, we can deduce:
o A+= {A, D, E} in R2 .
 Since A+ includes all a ributes of R2, A is a superkey for R2.

Since A is a superkey in both R1 and R2, the decomposition of R into R1= (A, B, C) and R2= (A, D,
E) satisfies the lossless-join condition.
Thus, the decomposition is lossless-join.

(b) State some advantages and disadvantages of Normaliza on. (5)

-> Advantages of Normaliza on:
1. Reduces Data Redundancy: Ensures that data is stored only once, reducing duplica on
across the database.
 DBMS PYQ 2020
2. Prevents Anomalies: Minimizes inser on, update, and dele on anomalies by organizing
data into related tables.
3. Improves Data Integrity: Enforces consistency and accuracy by ensuring that each piece
of data is stored in only one place.
4. Enhances Query Performance: Streamlined and well-structured tables can lead to more
efficient query execu on.
5. Facilitates Maintenance: Easier to manage and update data, as changes need to be
made in fewer places.
Disadvantages of Normaliza on:
1. Complex Queries: Normalized databases o en require more complex joins and queries,
which can be harder to write and understand.
2. Performance Overhead: The need for frequent joins can slow down query performance,
especially in read-heavy databases.
3. Ini al Design Complexity: Designing a normalized database requires more effort and a
thorough understanding of the data rela onships.
4. Poten al for Over-Normaliza on: Excessive normaliza on can lead to too many small
tables, complica ng database management.
5. Reduced Flexibility: Changes in database structure or requirements can require
extensive modifica ons in a highly normalized database.

15. (a) Differen ate between sparse index and dense index. (3)
-> Sparse Index:
o Contains index entries for only some of the records, typically one entry per block or a
group of records.
o Requires fewer index entries, saving storage space but may require more me to
search.
o Suitable for larger databases with fewer updates.
Dense Index:
o Contains index entries for every record in the database table.
o Provides faster search performance as every record can be quickly located.
o Requires more storage space and is suitable for smaller databases or those with
frequent lookups.

(b) With example explain how secondary index is used in database applica on. (4)
-> A secondary index is used to speed up queries on non-primary key a ributes, allowing efficient
data retrieval based on these a ributes.
Example: Consider a STUDENT table with the following a ributes:
 student_id (Primary Key)
 name
 department
If queries frequently search for students by their department, a secondary index on the department
column can be created. Without this index, the database would need to scan the en re table to find
students in a specific department. With the secondary index, the database can quickly locate the
relevant rows.
Usage:
Suppose you want to find all students in the 'Computer Science' department. The secondary
index on the department column allows the database to directly access the records without
scanning the whole table, significantly improving query performance.
 DBMS PYQ 2020
(c) Give an example where you will prefer the following: (4)
(i) Indexing in database.
(ii) Hashing in database.
-> Indexing in Database:
 Defini on: Indexing involves crea ng a data structure that improves the speed of data
retrieval opera ons on a database table. It allows efficient searching, especially for range-
based queries.
 Example: In an online bookstore, indexing is preferred for range-based queries, such as
finding all books within a price range or published within a specific year. Indexing the price
and publica on_year columns in the BOOKS table allows efficient retrieval of these records
without scanning the en re table.
Hashing in Database:
 Defini on: Hashing is a technique to map data to a fixed-size value, known as a hash, which
is used to quickly locate data in a hash table. It is ideal for exact match queries.
 Example: In a student management system, hashing is preferred for quick lookups of student
records by their unique student_id. Hashing the student_id column in the STUDENT table
allows constant me retrieval of a student's details, making it ideal for exact match queries
such as verifying a student's iden ty during login.