JAWAHARLAL NEHRU TECHNOLOGICAL UNIVERSITY HYDERABAD B.Tech II DATABASE MANAGEMENT SYSTEMS Previous Question Paper (November/December - 2020 - R15)

The overall system structure of a Database Management System (DBMS) is designed to manage data efficiently, ensure data integrity, and provide a secure interface for users and applications. The architecture is broadly categorized into several functional components that interact to process queries and manage physical storage.

The primary components of the DBMS architecture include:

• Query Processor: This component is responsible for interpreting and executing user queries. It includes the DDL interpreter (for schema definitions), the DML compiler (for translating data manipulation commands), and the query evaluation engine, which executes low-level operations generated by the query optimizer.
• Storage Manager: This is the interface between the low-level data stored in the database and the application programs. It includes the authorization and integrity manager, the transaction manager (ensuring ACID properties), the file manager, and the buffer manager (which handles data transfer between disk and main memory).
• Disk Storage: This represents the physical layer where data is stored. It consists of data files (the actual records), the data dictionary (metadata about the schema), indices (for faster data retrieval), and statistical data used by the query optimizer.

The system structure also distinguishes between different types of users and their interfaces:

• Users and Interfaces: Database administrators (DBAs) use DDL tools to define schemas; application programmers use DML interfaces to write programs; and naive users interact via graphical interfaces or report generators.
• Query Optimizer: A critical component that analyzes various execution plans for a query and selects the one with the lowest cost, typically in terms of I/O operations and CPU time.

By separating the physical storage from the logical view, the DBMS structure achieves data independence, allowing the underlying storage structures to be modified without affecting the external applications or user views.

An ER diagram for a University Admission System requires capturing entities, relationships, and extended features like specialization and aggregation. The core entities include Student, Department, Course, and Admission_Officer.

The entities and their attributes are as follows:

• Student: Student_ID (PK), Name, DOB, Email, Phone, Address (Composite attribute).
• Department: Dept_ID (PK), Dept_Name, Location.
• Course: Course_ID (PK), Course_Name, Credits.
• Admission_Officer: Officer_ID (PK), Name, Designation.

Relationships include:

• Applies_To: A many-to-one relationship between Student and Department.
• Offers: A one-to-many relationship between Department and Course.
• Processes: A many-to-many relationship between Admission_Officer and Student, resulting in an Admission_Record entity (Aggregation).

Extended features incorporated:

• Specialization: The Student entity can be specialized into Undergraduate_Student and Postgraduate_Student, sharing common attributes but having unique requirements (e.g., GRE/GATE scores for PG).
• Weak Entity: Dependent (for emergency contact) is a weak entity identified by the Student_ID.
• Derived Attribute: Age is a derived attribute calculated from the DOB of the student.
• Multivalued Attribute: Previous_Degrees is a multivalued attribute for the Student entity.

In this design, the Admission_Record acts as an associative entity (aggregation) linking the Student, the chosen Department, and the Admission_Officer to track the final status of the application.