Past Year Questions of DBMS(2020-2024 and Model Questions)

A Database is a structured collection of data that can be stored in both physical and digital form for easy and simple access and management. For eg: A phonebook is an example of physical database and an online banking system can be example of digital database.
A Database Management System (DBMS) is software that manages digital databases, providing users and applications an interface to interact with data efficiently.

Advantages of DBMS are reduced data redundancy(data isnt repeated), improved data integrity i.e. it ensures that data is recorded exactly as intended and remains unchanged and uncorrupted during storage, transfer, and retrieval, enhanced data security, efficient data access, support for backup and recovery, and concurrent user access i.e. multiple users can utilize the database without interfearing with eachother.
It's Disadvantages are increased system complexity, high cost, slower compared to simple file systems due to advanced features such as data integrity, security and querying , and potential security vulnerabilities if not managed properly.

There are several types of DBMS models used to organize and manage data. Each model has its own structure, advantages, and disadvantages:

1. Hierarchical Model:
Data is organized in a tree-like structure using parent-child relationships, where each child has only one parent.

Advantages:
– Simple and easy to understand.
– Efficient for data with a clear hierarchy (e.g., organizational charts).

Disadvantages:
– Rigid structure; difficult to modify.
– Cannot efficiently represent many-to-many relationships.
– Requires data duplication in certain cases.

2. Network Model:
Similar to the hierarchical model but allows many-to-many relationships through the use of sets and pointers.

Advantages:
– More flexible than the hierarchical model.
– Supports complex relationships and efficient access.

Disadvantages:
– Complex structure; difficult to design and maintain.
– Requires knowledge of physical data paths.

3. Relational Model:
Data is stored in tables (called relations) consisting of rows and columns. It is the most widely used model today.

Advantages:
– Simple and easy to understand.
– Uses powerful query language (SQL).
– Ensures data integrity and minimizes redundancy.

Disadvantages:
– May not perform as well with very large or complex data structures.
– Can be inefficient in handling hierarchical or graph-based data.

4. Object-Oriented Model:
Data is stored as objects, similar to how it is handled in object-oriented programming languages. It supports complex data types.

Advantages:
– Suitable for complex data types like multimedia and engineering designs.
– Integrates well with object-oriented programming.

Disadvantages:
– Less widely adopted.
– More complex to design and manage.
– Limited support and tools compared to relational DBMS.

A relational database is a type of database which stores data in the form of tables called relations.Each table consists of rows called records and columns called attributes. In this, keys are used to to establish and enforce relationships between tables thus data integrity is maintained and redundancy is reduced. SQL (Structured Query Language) is the most commonly used language to manage such databases.

Relational databases differ from other models in several ways:
– Unlike the hierarchical model, which organizes data in a tree structure, relational databases use tabular format, making data more flexible and easier to manage.
– Unlike the network model, which allows complex many-to-many relationships using pointers, relational databases use foreign keys and join operations to represent such relationships.
– Compared to object-oriented databases, relational databases are simpler and more widely supported, but may not handle complex data types as efficiently.

Relational databases are preferred for their simplicity, scalability, and support for powerful query languages.

Dublication/ repetation of the same data multiple times in a database is called data redundancy. An exaple of data redundancy can be taken when contact info of a customer is stored in seperate files. This means that if the contact info has to be updated, then it should be done on all files else there would be inconsistency. DBMS helps prevent this by using a centeralized data storage and using relations and normalization to break down the table where common data is linked using keys. So update can be made in a single place hence saving time, ensuring consistency and less storage is used.

A Database Administrator (DBA) is a person responsible for the installation, configuration, management, maintenance, and security of a database system. The DBA ensures that databases run efficiently, securely, and are always available for users and applications.

Major responsibilities of a DBA include:
– Installing and configuring the DBMS software.
– Creating and managing database structures such as tables, indexes, and views.
– Monitoring database performance and tuning it for efficiency.
– Managing user access, roles, and permissions to ensure data security.
– Performing regular backups and planning recovery strategies in case of data loss.
– Ensuring data integrity and enforcing database standards.
– Applying software patches and updates.
– Coordinating with developers and system administrators to support application requirements and database integration.

Normalization is the process of organizing data in a database to reduce redundancy (duplicate data) and ensure data integrity. It involves dividing large tables into smaller, related tables and linking them using keys. This makes data easier to maintain, reduces the chance of anomalies (like update or deletion issues), and improves database efficiency.

First Normal Form (1NF):
A table is in 1NF if:
– All values in each column are atomic (indivisible).
– Each record is unique.

Example (Not in 1NF):

StudentID	Name	Subject
1	Ram	Math,English
2	Sita	Science,Math

Conversion to 1NF:

StudentID	Name	Subject
1	Ram	Math
1	Ram	English
2	Sita	Science
2	Sita	Math

Second Normal Form (2NF):
A table is in 2NF if:
– It is in 1NF.
– All non-key attributes are fully functionally dependent on the primary key (no partial dependency).

Example (In 1NF not 2NF):

StudentID	Subject	Teacher
1	Math	Mr. Sharma
1	English	Ms. Koirala
2	Science	Mr. Lama

Here, TeacherName depends only on Subject, not the whole composite key (StudentID + Subject).

Conversion to 2NF:
Main Table:

StudentID	Subject
1	Math
1	English
2	Science

Subject-Teacher Table:

Subject	TeacherName
Math	Mr. Sharma
English	Ms. Koirala
Science	Mr. Lama

Third Normal Form (3NF):
A table is in 3NF if:
– It is in 2NF.
– All attributes are only dependent on the primary key, and not on any other non-key attribute (no transitive dependency).

Example (Not in 3NF):

StudentID	StudentName	Department	HOD
1	Ram	Science	Dr. Sharma
2	Sita	Arts	Dr. Joshi

Here, HOD depends on Department, not directly on StudentID.

Conversion to 3NF:
Student Table:

StudentID	StudentName	Department
1	Ram	Science
2	Sita	Arts

Department Table:

Department	HOD
Science	Dr. Sharma
Arts	Dr. Joshi

Normalization Process Summary:
1. Remove repeating groups → 1NF
2. Remove partial dependencies → 2NF
3. Remove transitive dependencies → 3NF

By following these steps, the database becomes more structured, efficient, and easier to maintain.

Primary Key:
A primary key is a field (or a combination of fields) that uniquely identifies each record in a table. It cannot have duplicate or NULL values.

Example: In a Student table:

StudentID	Name	Class
1	Ram	12
2	Sita	11

Here, StudentID is the primary key because it uniquely identifies each student.

Foreign Key:
A foreign key is a field in one table that refers to the primary key of another table. It is used to establish a relationship between two tables.

Example: In a Marks table:

StudentID	Subject	Marks
1	Math	85
2	English	90

Here, StudentID is a foreign key because it refers to StudentID in the Student table.

Candidate Key:
A candidate key is any field or combination of fields that can uniquely identify a record in a table. A table can have multiple candidate keys, but only one is selected as the primary key.

Example: In a Student table:

StudentID	Email	Phone
1	ram@gmail.com	9800000001
2	sita@gmail.com	9800000002

Here, StudentID, Email, and Phone are all candidate keys because each can uniquely identify a student. One of them (e.g., StudentID) is chosen as the primary key.

SQL (Structured Query Language) is a standard language used to manage and manipulate data in a relational database. It allows users to create, read, update, and delete data easily using commands.

SQL is divided into several important components based on what they do:

1. DDL (Data Definition Language):
These commands are used to define the structure of database objects like tables.
– CREATE: Creates a new table or database.
– ALTER: Modifies the structure of an existing table (e.g., adding a column).
– DROP: Deletes a table or database completely.

2. DML (Data Manipulation Language):
These commands are used to manage data inside tables.
– INSERT: Adds new data to a table.
– UPDATE: Changes data in a table.
– DELETE: Removes data from a table.

3. DQL (Data Query Language):
This part is used to query or view data from the database.
– SELECT: Retrieves specific or all records from one or more tables.

4. DCL (Data Control Language):
These commands are used to control who can access or change the data.
– GRANT: Gives permission to a user.
– REVOKE: Takes back permission from a user.

Common SQL Functions:
– INSERT INTO table_name VALUES (...): Adds a new record.
– SELECT * FROM table_name: Shows all data from a table.
– UPDATE table_name SET column = value WHERE condition: Changes specific data.
– DELETE FROM table_name WHERE condition: Deletes specific rows based on a condition.

In summary, SQL is a powerful language used to interact with databases in a structured and efficient way.

CODE:

  CREATE DATABASE CompanyDB;
  USE DATABASE CompanyDB;
  CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50),Age INT,Department VARCHAR(50));

  INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Department)
  VALUES 
        (1, 'Ram', 'Singh', 30, 'HR'),
        (2, 'Virat', 'Smith', 25, 'IT'),
        (3, 'Micheal', 'Jordon', 40, 'Finance'),
        (4, 'Sherlock', 'Holmes', 35, 'Marketing');
  SELECT * FROM Employees
  WHERE EmployeeID = 1;

  

OUTPUT:

WHAT IT DOES:
The CREATE command is used to create DATABASES, TABLES, etc.
The INSERT command is used to add new records into the tables. eg: if i want to add data of new employee i'd use INSERT command.
The SELECT command is used to search for the records that satisfy the provided condition. In this case where EmployeeID = 1 .i.e it shows all the deatil of the employee whose id is 1.