1. Database Management System (DBMS) [Most Important Questions and Solutions]

 

2081)Evaluate the advantages of DBMS compared to traditional file-based data storage systems.

2080 GIE Set A) What is the importance of database? Describe.

Ans:

Database: A structured collection of interrelated data and information organized for easy access, management, and updates (e.g., telephone directory, student record).

DBMS: A DBMS (Database Management System) is a set of programs that manages (define, store and manipulate) the data stored in a database. Examples: MS-Access, Oracle, MySQL, SQL Server, dBase, etc.

Advantages of DBMS (Importance of database):

1)      Data integrity: A database system enforces data constraints and relationships, ensuring that the data is accurate and consistent.

2)      Data independence: A database system allows for changes to be made to the structure of the data without affecting the applications that use the data.

3)      Concurrent access: A database system allows multiple users to access the same data simultaneously, without interfering with each other.

4)      Data security: A database system provides various security mechanisms to control access to the data and prevent unauthorized access.

5)      Data sharing: A database system allows for data to be easily shared among different applications and users.

6)      Data Backup and Recovery: Database system provides various options for data backup and recovery, which makes it easy to recover data in case of any failure.

7)      Data redundancy and inconsistency can be reduced.

 

2081 GIE Set A) List the different types of database model. Explain Relational Database model in brief.

2080/2079 GIE Set A/2079 Set D) Explain relational database model with example.

2081 GIE Set A OR) Describe the relational data model.

Ans: The different types of database model are as follows:

1.      Hierarchical Database Model

2.      Network Database Model

3.      Relational Database Model

4.      Entity–Relationship (ER) Model

5.      Object-Oriented Database Model

Relational Database Model:

The Relational database model is a worldwide accepted, commonly used database model in which data are logically organized in the form of a two-dimensional table. It was proposed by E. F. Codd in 1970.

The name relational database model is derived from the fact that each table represents a relation, and each row of the table represents a single record (also known as tuple), while each column represents an attribute (also known as field).

Key Concepts of the Relational Data Model include:

1.      Relation (Table): A relation is a table consisting of rows and columns.

2.      Tuple (Row): A tuple represents a single record in a table.

3.      Attribute (Column): An attribute represents a field or property of the data.

4.      Primary Key: A primary key uniquely identifies each record in a table.

5.      Foreign Key: A foreign key is an attribute that creates a relationship between two tables.

Advantages of Relational Database Model is as follows:

1.      Very less data redundancy.

2.      Normalization of the database is possible.

3.      Database processing is faster than another model.

4.      Data searching is faster than another model.

Example of Relational Data Model is as follows:

Here, the relationship is established via the sid (Student ID) field.

Ø  In the Student Table: sid is the Primary Key, uniquely identifying each student.

Ø  In the Class Table: sid is the Foreign Key, linking back to the student records.

 

2082 OR) What is normalization? Explain 2NF and 3NF. [2+3]

Ans: Normalization is a database design process of breaking down or decomposing a complex relation (table) into simple multiple relations to minimize redundancy and dependency.

Ø  It reduces redundancy and dependency using principle of non-loss decomposition in which a large table is reduced to smaller tables without loss of information.

The most common widely used normal forms are as follows:

1.   First Normal Form (1NF)

2.   Second Normal Form (2NF)

3.   Third Normal Form (3NF)

4.   Boyce Codd Normal Form (BCNF)

5.   Fourth Normal Form (4NF)

1) Second Normal Form (2NF):

A relation or table is said to be in second normal form (2NF) if it is already in First Normal Form (1NF) and its all non-key attributes are fully functionally dependent on the Primary Key.

The purpose of 2NF is to eliminate partial key dependencies.

To convert the table in 1NF into 2NF, the non-key attributes which are not fully functionally dependent on the Primary Key are decomposed into separate table.

2) Third Normal Form (3NF):

A relation or table is said to be in third normal form (3NF) if it is already in Second Normal Form (2NF) and it has no transitive(indirect) dependencies (non-key attributes don't depend on other non-key attributes)

In simpler terms, in 3NF, every non-key column must depend only on the primary key, not on any other columns in the table.

The purpose of 3NF is to eliminate transitive key dependencies.

 

2081 GIE Set A) Evaluate the normalization process in relational database. Provide examples to illustrate 2NF normalization form.

Ans: Normalization is a database design process of breaking down or decomposing a complex relation (table) into simple multiple relations to minimize redundancy and dependency.

It reduces redundancy and dependency using principle of non-loss decomposition in which a large table is reduced to smaller tables without loss of information. Normalization is achieved through a series of normal forms as follows:

1. First Normal Form (1NF)

2. Second Normal Form (2NF)

3. Third Normal Form (3NF)

4. Boyce Codd Normal Form (BCNF)

5. Fourth Normal Form (4NF)

 

An example to illustrate 2NF normalization form is follows:

Original Table (Before 2NF):

StudentID	Class	StudentName	Subject	Marks
1	10	Ram	English	80
1	10	Ram	Math	75
2	10	Hazi	English	85
2	10	Hazi	Math	70

Primary Key: (StudentID, Subject)

Here this table is not in 2NF because StudentName and Class depend only on StudentID.

 

After 2NF Decomposition:

We split the table into 3 smaller tables as follows:

Table 1: Students

StudentID	StudentName	Class
1	Ram	10
2	Hazi	10

Primary Key: StudentID

 

Table 2: Subjects

SubjectID	SubjectName
101	English
102	Math

Primary Key: SubjectID

Table 3: Marks

StudentID	SubjectID	Marks
1	101	80
1	102	75
2	101	85
2	102	70

Primary Key: (StudentID, SubjectID)

 

Conclusion: This example illustrates how Second Normal Form (2NF) eliminates partial dependency.

Ø  In the original table, StudentName and Class depended only on StudentID, not on the full primary key (StudentID, Subject).

Ø  By decomposing the table into Students, Subjects, and Marks, all non-key attributes now fully depend on their respective primary keys.

 

2080 GIE Set B) Describe the second normal form (2NF) with an example.

Ans: A relation or table is said to be in second normal form (2NF) if it is already in First Normal Form (1NF) and its all non-key attributes are fully functionally dependent on the Primary Key.

Ø  The purpose of 2NF is to eliminate partial key dependencies.

Ø  To convert the table in 1NF into 2NF, the non-key attributes which are not fully functionally dependent on the Primary Key are decomposed into separate table.

Example: Look on 2081 GIE Set A)

 

2082 GIE Set B) What is normalization? Explain 2NF.  [2+3]

Ans: Look on 2082 OR) and 2081 GIE Set A)

 

2079 Set A OR) What are the purposes of normalization? Give an example of 3NF.

Ans: Normalization is a database design process of breaking down or decomposing a complex relation (table) into simple multiple relations to minimize redundancy and dependency.

The purposes(advantages) of normalization are as follows:

1)       It reduces data redundancy (duplication of data).

2)       It removes different data anomalies, such as insertion, update, and deletion anomaly.

3)       It makes the data model more flexible and easier to maintain.

4)       It identifies dependency between the data fields.

5)       It improves faster sorting and indexing.

 

An example to illustrate 3NF normalization form is follows:

Original Table (Before 3NF):

StudentID	StudentName	Subject	SubjectCode
1	Ramesh	Mathematics	M101
2	Sita	Science	S102
3	Gopal	Mathematics	M101

Primary Key: StudentID
Here this table is not in 3NF because there's a transitive dependency: SubjectCode → Subject→ StudentID

 

After 3NF Decomposition:

We split the table into 2 smaller tables as follows:

Table1: Student

StudentID	StudentName	Subject
1	Ramesh	Mathematics
2	Sita	Science
3	Gopal	Mathematics

 

Table2: Subject

Subject	SubjectCode
Mathematics	M101
Science	S102

Now all transitive dependencies are removed, and each table is in 3NF. The non-key columns in each table depend only on the primary key of that table.

 

 

2081 OR) How does Second Normal Form (2NF) differ from First Normal Form (1NF), and what are the key benefits of achieving 2NF in database design? Explain. [2+3]

Ans:

A table is in First Normal Form if all its attributes are atomic, meaning every cell contains only a single value and there are no repeating groups.

A relation or table is said to be in second normal form (2NF) if it is already in First Normal Form (1NF) and its all non-key attributes are fully functionally dependent on the Primary Key.

Second Normal Form (2NF) differ from First Normal Form (1NF) as given in difference table below:

1NF	2NF
It eliminates repeating groups and multivalued attributes.	It eliminates partial dependencies.
It allows partial dependency.	It does not allow partial dependency.
It requires atomic (indivisible) values.	It requires full functional dependency.
It may still cause data redundancy.	It reduces data redundancy.

Key Benefits of Achieving 2NF in database design are as follows:

1.      Eliminates partial dependency, ensuring better data organization.

2.      Reduces data redundancy and repeated data.

3.      Improves data consistency and integrity.

4.      Simplifies data maintenance and updates.

5.      Makes database design more efficient and structured.

 

 

Q1) Write SQL DDL command to execute the following task with reference to the schema given below: student_info(regno as integer, name as character(25), class integer,gender character(1), address character(25)).           

Ans:

The SQL Data Definition Language (DDL) command to create the student_info table with the specified schema is as follows:

CREATE TABLE student_info (

    regno INT PRIMARY KEY,

    name VARCHAR(25),

    class INT,

    gender VARCHAR(1),

    address VARCHAR(25)

);

Output:

Student_info

regno	name	class	gender	address
empty

Explanation of above command:

1.      Creates a new table named student_info

2.      Defines five columns with the specified data types:

Ø  regno as INTEGER

Ø  name as CHARACTER(25) (fixed-length string of 25 characters)

Ø  class as INTEGER

Ø  gender as CHARACTER(1) (single character)

Ø  address as CHARACTER(25) (fixed-length string of 25 characters)

 

2081 GIE Set A OR) Write the SQL DDL statement to create an employee table with the mentioned schema with following attributes.

Field	Data type	Constraints
Employee ID	INT	PRIMARY KEY
Name	CHAR(30)
Address	CHAR(20)
Gender	CHAR(2)
Post	CHAR(15)

 

Ans:

The SQL DDL (Data Definition Language) statement to create the employee table based on the given schema is as follows:

CREATE TABLE employee (

    EmployeeID INT PRIMARY KEY,

    Name VARCHAR(30),

    Address VARCHAR(20),

    Gender VARCHAR(2),

    Post VARCHAR(15)

);

Output:

Employee

EmployeeID	Name	Address	Gender	Post
empty

Explanation of above command:

Ø  EmployeeID is of type INT and is set as the Primary Key.

Ø  Name, Address, Gender, and Post use the CHAR datatype with specified lengths as per the question.

Ø  No additional constraints are given, so only the primary key is defined.

 

2081 GIE Set B OR) Write any five differences between centralized and distributed database model.

2079 Set A/2076 GIE Set B/2075 Set A) Differentiate the centralized and distributed database system.

Ans: A centralized database is a database system where all data is stored, managed, and processed in a single location (e.g., one server or data center).

A distributed database is a database system where data is stored across multiple physical locations, either on different servers, data centers, or even geographically dispersed regions. 

Any five differences between centralized and distributed database model are as follows:

Basis of Comparison	Centralized Database	Distributed Database
Location of Data & System Architecture	Ø Data is stored in a single location/server. Ø Uses a single system.	Ø Data is stored across multiple locations/servers. Ø Uses multiple interconnected systems (nodes).
Availability & Performance	Ø Low availability – failure of central server causes downtime.   Ø May have performance bottleneck under high load.	Ø High availability – failure of one node does not affect the system. Ø Better performance due to load distribution.
Scalability & Data Access Speed	Ø Limited scalability to single machine. Ø Slower access for remote users.	Ø Easily scalable by adding nodes. Ø Faster local access as data is closer to users.
Consistency & Complexity	Ø Easier to maintain strong consistency. Ø Simple design and maintenance.	Ø Harder to maintain due to data replication and synchronization. Ø Complex architecture requiring synchronization.
Cost & Performance	Ø Lower infrastructure cost. Ø Performance bottleneck under high load.	Ø Higher cost due to multiple servers. Ø Better performance due to load distribution.
Backup & Recovery	Easier backup – only one system to manage.	Complex backup – coordinated across locations.
Examples	MySQL (single server), Oracle (non-clustered), MS SQL Server	Google Spanner, MongoDB (sharded), Cassandra, Amazon DynamoDB

 

2082) Write any three differences between DDL and DML. Give examples of each. 

Ans:

DDL (Data Definition Language)	DML (Data Manipulation Language)
It is a declarative subset of SQL that defines and manages the structure of the database.	It is an imperative subset of SQL that retrieves and manipulates the data stored in the database.
Changes made using DDL affect the structure of the database.	Changes made using DML affect the data stored in the database.
DDL commands are auto-committed and permanent; they cannot be rolled back.	DML commands are not auto-committed; they can be rolled back.
Common DDL commands are: CREATE, DROP, ALTER, TRUNCATE, and RENAME.	Common DML commands are: INSERT, SELECT, UPDATE, and DELETE.
DDL commands do not use a WHERE clause.	DML commands can use a WHERE clause.
DDL is not further classified.	DML can be classified as Procedural and Non-procedural.

 

DDL Example (CREATE TABLE):

CREATE TABLE Students (

    SID INT PRIMARY KEY,

    Name VARCHAR(50),

    Class INT

);

Ø  This command creates a table named Students with three columns: SID, Name, and Class.

DML Example (INSERT):

INSERT INTO Students (SID, Name, Class)

VALUES (1, 'Ram', 10);

Ø  This command inserts a record into the Students table.

 

2082 GIE Set B) Write two DDL Commands and three DML Commands in SQL with Syntax.

Ans:

Any two DML Commands are as follows:

1. CREATE TABLE:

Syntax:

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    ...

);

Example:

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    Name VARCHAR(50),

    Class INT

);

 

2. ALTER TABLE

Syntax:

ALTER TABLE table_name

ADD column_name datatype;

Example:

ALTER TABLE Students

ADD Age INT;

 

Any three DML Commands are as follows:

1. INSERT

Syntax:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Example:

INSERT INTO Students (StudentID, Name, Class)

VALUES (1, 'Ram', 10);

 

2. SELECT

Syntax:

SELECT column1, column2

FROM table_name;

Example:

SELECT * FROM Students;

 

3. UPDATE

Syntax:

UPDATE table_name

SET column_name = value

WHERE condition;

Example:

UPDATE Students

SET Class = 11

WHERE StudentID = 1;

 

2079 Set A) Describe the DDL statement with an example in SQL.

2079 GIE Set A) Demonstrate any two DDL statements with an example.

Ans: DDL (Data Definition Language) is a declarative subset of SQL that defines and manages the structure of the database and its objects (tables, indexes, etc.). Common DDL commands (statements) are as follows:

1)      CREATE: to create database objects

2)      ALTER: to modify existing objects

3)      DROP: to delete objects

4)      TRUNCATE: to remove all records from a table

5)      RENAME: to rename database objects

 

Example of DDL Statements are as follows:

1. CREATE TABLE:

Purpose: To create a new table in the database.

Example:

CREATE TABLE Students (

    SID INT PRIMARY KEY,

    Name VARCHAR(50),

    Class INT

);

Ø  This command creates a table named Students with three columns: SID, Name, and Class.

 

2. ALTER TABLE:

Purpose: To modify the structure of an existing table.

Example:

ALTER TABLE Students

ADD Age INT;

Ø  This command adds a new column Age to the Students table.

 

3. DROP TABLE:

Purpose: To delete an existing table from the database permanently.

Example:

DROP TABLE Students;

Ø  This command removes the Students table completely from the database along with all its data and structure.

 

4. TRUNCATE TABLE:

Purpose: To remove all records from a table while preserving its structure.

Example:

TRUNCATE TABLE Students;

Ø  This command deletes all rows from the Students table permanently, but the table structure remains unchanged.

 

2076) Describe the DDL statement with an example in SQL.

Ans: DML (Data Manipulation Language) is an imperative subset of SQL that retrieves and manipulates data stored in database objects (tables, indexes, etc.). Common DML commands (statements) are as follows:

1)      INSERT: to add new records

2)      SELECT: to retrieve data

3)      UPDATE: to modify existing records

4)      DELETE: to remove records

 

Example of DML Statement are as follows:

1. INSERT:

Purpose: To add new records to a table.

INSERT INTO Students (SID, Name, Class)

VALUES (1, 'Ram', 10);

Ø  This command inserts a record into the Students table.

 

2. SELECT:

Purpose: To retrieve data from a table.

SELECT * FROM Students;

Ø  This command retrieves all records from the Students table.

 

3. UPDATE:

Purpose: To modify existing records in a table.

UPDATE Students

SET Class = 11

WHERE SID = 1;

Ø  This command updates the record of the student whose SID = 1, changing the Class value from 10 to 11 in the Students table.

 

4. DELETE:

Purpose: To remove records from a table.

DELETE FROM Students

WHERE SID = 1;

Ø  This command deletes the record of the student whose SID = 1 from the Students table.

 

2079 GIE Set B) What are the roles of the database administrator? Describe.
Ans: The roles and responsibilities of the DBA (Database Administrator) are as follows:

1.      Database Installation and Configuration:
Setting up the DBMS software and configuring it for optimal performance.

2.      Backup and Recovery:
Scheduling regular backups and planning for disaster recovery to prevent data loss.

3.      Performance Monitoring and Tuning:
Analyzing query performance and optimizing indexes, queries, and server resources.

4.      Security Management:
Creating user accounts, assigning roles, and enforcing access control policies.

5.      Database Design and Schema Management:
Assisting in logical and physical database design based on application needs.

6.      Data Integrity and Availability:
Ensuring data is accurate, consistent, and available to authorized users when needed.

 

2080 GIE Set B OR) What are the importance of database security in database management systems? Describe.

Ans: Data Security refers to the protective measures and protocols implemented to safeguard digital data from unauthorized access, corruption, theft, or damage throughout its lifecycle.

Database security plays a critical role in any DBMS because it protects valuable data assets from unauthorized access, misuse, corruption, or loss. Below are the key points that describe the importance of database security:

1. Protects Sensitive Data: Secures confidential information like personal, financial, and health records from unauthorized access.

2. Ensures Legal Compliance: Helps meet regulations (e.g., GDPR, HIPAA), avoiding legal penalties.

3. Prevents Data Breaches: Reduces the risk of exposing critical or private data to outsiders.

4. Maintains Data Integrity: Prevents unauthorized changes, ensuring data remains accurate and reliable.

5. Ensures Data Availability: Provides continuous access to authorized users, even during failures or attacks.

6. Controls Access: Restricts data access and actions based on user roles, preventing misuse or accidental damage.

7. Enables Monitoring & Accountability: Tracks user activity via logs and audits to detect misuse and promote transparency.