PART I (Very short Answer)

1. List any four advantages of using a DBMS.

 Data Redundancy and Inconsistency: Reduced by integrating all the data into a
single database.

 Data Access: Enhanced through standardized query languages.

 Data Security: Enforced via authorization protocols.

 Data Integrity: Maintained through integrity constraints.

2. What is a schema?

 A schema is the structure that defines the organization of data in a database,

including tables, fields, relationships, views, indexes, and other elements.

3. What is an E-R Diagram?

 An Entity-Relationship Diagram (ERD) is a graphical representation of entities and

their relationships within a database system.

4. Differentiate between super key and candidate key.

 Super Key: A set of one or more columns that uniquely identifies a record in a table.

 Candidate Key: A minimal super key, meaning it cannot be reduced further and still
uniquely identify a record.

5. What is a transaction?

 A transaction is a sequence of operations performed as a single logical unit of work,

which is either entirely completed or entirely failed, ensuring data consistency.

6. What is a Join statement?

 A Join statement in SQL is used to combine rows from two or more tables based on a
related column between them.

7. What is a subquery?

 A subquery is a query nested inside another query, used to provide results for the
main query.

8. Why is normalization required?

 Normalization is required to eliminate redundancy, ensure data dependencies make

sense, and improve data integrity.

9. How is a transaction committed in a distributed system?

 In a distributed system, a transaction is committed using a two-phase commit

protocol (2PC) to ensure all participating nodes either commit or roll back the
transaction.

10. What is ODL?

  ODL (Object Definition Language) is a language used to define data types, structures,
and constraints in an object-oriented database.

PART II (Short answer)

1. Differentiate between Logical and Physical data independence.

 Logical Data Independence: The ability to change the logical schema without
altering the external schema or application programs.

 Physical Data Independence: The ability to change the physical schema without
affecting the logical schema.

2. What is aggregation? Give an example.

 Aggregation is a concept where a relationship set is treated as an entity set for the
purpose of abstraction. For example, if a 'Project' entity set has a relationship with
both 'Department' and 'Employee' entity sets, this relationship can be aggregated
into a higher-level 'Works_On' entity.

3. Explain the structure of SQL SELECT statement.

 The basic structure of an SQL SELECT statement is:

SELECT [DISTINCT] column1, column2, ... FROM table1 [WHERE condition] [GROUP BY column1,
column2, ...] [HAVING condition] [ORDER BY column1, column2, ... [ASC|DESC]];

4. What is access control?

 Access control is a security technique that regulates who or what can view or use
resources in a computing environment, ensuring only authorized users can access
certain data.

5. What do you mean by concurrency control?

 Concurrency control in databases ensures that multiple transactions can occur

simultaneously without leading to inconsistencies or conflicts, using mechanisms like
locks, timestamps, and isolation levels.

PART III (Long answer)

1. Define DBMS. What are the advantages of DBMS over conventional file processing system?
What are the functions of a database administrator?

 DBMS: A Database Management System (DBMS) is a software system that uses a

standard method of cataloging, retrieving, and running queries on data.

 Advantages over File Processing:

 Reduced data redundancy and inconsistency

 Improved data sharing and security

 Enhanced data integrity and independence

 Centralized data management

 Functions of a DBA:
  Database design and implementation

 Ensuring data security and integrity

 Performance tuning

 Backup and recovery

 User access control

2. Explain the rules defined by Codd that are necessary for any DBMS to be considered as a
RDBMS.

 E.F. Codd defined 12 rules (Codd's 12 rules) which a DBMS must follow to be
considered a true RDBMS. These include the Information rule, Guaranteed access
rule, Systematic treatment of null values, Active online catalog, etc.

3. Differentiate between Strong and Weak entity, Referential and Domain integrity, Single
valued and multi valued attributes.

 Strong vs. Weak Entity: A strong entity can exist independently while a weak entity
depends on a strong entity.

 Referential vs. Domain Integrity: Referential integrity ensures that a foreign key
value always points to an existing record. Domain integrity ensures that all entries in
a column are of the same data type.

 Single Valued vs. Multi Valued Attributes: Single valued attributes have a single
value for each entity, while multi-valued attributes can have multiple values.

4. What is relational algebra? Explain different types of join and aggregate operations of
relational algebra. Give appropriate examples.

 Relational Algebra: A procedural query language that works on relational model

concepts.

 Types of Join:

 Inner Join: Combines tuples from two relations based on a

condition.

 Outer Join: Includes all tuples from one or both relations, filling in
with NULLs where no match is found.

 Cross Join: Combines all tuples from both relations (Cartesian

product).

 Aggregate Operations:

 SUM: Adds up values.

 AVG: Calculates the average.

 COUNT: Counts the number of tuples.

 MIN/MAX: Finds the minimum or maximum value.

5. Explain normalization & its different forms. Give appropriate examples.

  Normalization: The process of organizing data to minimize redundancy.

 1NF: Ensures all columns have atomic values.

 2NF: Ensures 1NF and all non-key attributes are fully functionally dependent
on the primary key.

 3NF: Ensures 2NF and no transitive dependencies.

 BCNF: A stronger version of 3NF.

6. Describe backup and recovery mechanisms available in DBMS.

 Backup Mechanisms:

 Full Backup: Backs up the entire database.

 Incremental Backup: Backs up changes since the last backup.

 Differential Backup: Backs up changes since the last full backup.

 Recovery Mechanisms:

 Point-in-time Recovery: Restores the database to a specific time.

 Transaction Log Backup: Uses logs to restore committed transactions.

7. Write SQL queries for the following:

 a) Display the names of the students who have grade 'C' or grade 'D'.

SELECT Name FROM SPORTS WHERE Grade IN ('C', 'D');

 b) Display the grade of the students whose name starts with 'D'.

SELECT Grade FROM SPORTS WHERE Name LIKE 'D%';

 c) Display the different games offered.

SELECT DISTINCT Game FROM SPORTS;

 d) Display the Roll number and name of the student who belongs to class '7' and
plays hockey.

SELECT RollNo, Name FROM SPORTS WHERE Class = '7' AND Game = 'Hockey';

 e) Delete the student record whose roll no. is 101.

DELETE FROM SPORTS WHERE RollNo = 101;

8. Explain the data types available in SQL. Also explain various aggregate functions in SQL
with suitable examples.

 Data Types:

 Numeric: INT, FLOAT, DECIMAL

 Character/String: CHAR, VARCHAR

 Date/Time: DATE, TIME, TIMESTAMP

  Aggregate Functions:

 COUNT(): Counts the number of rows.

SELECT COUNT(*) FROM SPORTS;

 SUM(): Sums up numeric values.

SELECT SUM(Salary) FROM EMPLOYEES;

 AVG(): Calculates the average value.

SELECT AVG(Age) FROM STUDENTS;

 MIN()/MAX(): Finds the minimum/maximum value.

SELECT MIN(Salary), MAX(Salary) FROM EMPLOYEES;

9. Explain the following:

 Distributed Transactions: Transactions that span multiple networked databases,

ensuring all-or-nothing commitment across all involved databases.

 Object-relational Databases: Databases that integrate object-oriented database

model features with relational databases.

10. Define object databases. Describe persistent programming languages. What are the several
approaches proposed to make the objects persistent?

 Object Databases: Databases that store data in the form of objects, as used in
object-oriented programming.

 Persistent Programming Languages: Languages that support the persistence of

objects beyond program execution.

 Approaches to Persistence:

 Serialization: Converting objects to a format suitable for storage.

 Object-Relational Mapping (ORM): Mapping objects to database tables.

 Database Support: Direct support for objects in databases.