IGNOU MCS-023 Solved Question Paper PDF Download

IGNOU MCS-023 Previous Year Solved Question Paper in Hindi

IGNOU MCS-023 Previous Year Solved Question Paper in English

Q1. (a) Draw an E-R diagram for a Hospital Management System (HMS) and convert it into a relational schema. Make necessary assumptions. (b) Normalize the following relation up to Boyce-Codd Normal form (BCNF) : EMP (EmpID, EmpName, DeptID, DeptName, DeptLocation). Add field, if necessary. Also explain the process. (c) Explain the transaction recovery process using log-based recovery and checkpointing with a suitable example. (d) Consider the following relation : STUDENT (SID, SName, Major, AdvisorID). Write relational algebra expressions to: (i) Get names of students majoring in ‘CS’. (ii) List all students advised by advisor ‘A077’. Make necessary assumptions.

Ans.

(a) E-R Diagram for Hospital Management System (HMS) and Relational Schema

Assumptions:

• A doctor can treat multiple patients, and a patient can be treated by multiple doctors (Many-to-Many relationship).
• A patient is admitted to only one ward at a time (One-to-One relationship).
• A patient can have multiple appointments, but an appointment is associated with one patient and one doctor.
• Each patient has a unique medical record (One-to-One relationship).

E-R Diagram: (Students should draw a diagram representing the following entities and relationships)

• Entities:
  • PATIENT ( PatientID , PName, Address, Phone)
  • DOCTOR ( DoctorID , DName, Specialization)
  • WARD ( WardNo , WardType)
  • APPOINTMENT ( ApptID , ApptDate, ApptTime)
  • MEDICAL_RECORD ( RecordID , Problem, Treatment_Date)
• Relationships:
  • Treats (An M:N relationship between DOCTOR and PATIENT).
  • Is_Admitted_In (A 1:1 relationship between PATIENT and WARD).
  • Has_Appointment (Connects PATIENT, DOCTOR, and APPOINTMENT).
  • Has_Record (A 1:1 relationship between PATIENT and MEDICAL_RECORD).

Conversion to Relational Schema: To convert the E-R diagram into a relational schema, a table is created for each entity and each M:N relationship.

1. PATIENT ( PatientID , PName, Address, Phone, WardNo) (WardNo is a foreign key to the WARD table)
2. DOCTOR ( DoctorID , DName, Specialization)
3. WARD ( WardNo , WardType, Capacity)
4. TREATS ( DoctorID , PatientID , Disease) (This table is for the M:N relationship ‘Treats’. DoctorID and PatientID are foreign keys to the DOCTOR and PATIENT tables, respectively.)
5. APPOINTMENT ( ApptID , ApptDate, ApptTime, PatientID, DoctorID) (PatientID and DoctorID are foreign keys to the PATIENT and DOCTOR tables, respectively.)
6. MEDICAL_RECORD ( RecordID , Problem, Treatment_Date, PatientID) (PatientID is a foreign key to the PATIENT table.)

(b) Normalization up to Boyce-Codd Normal Form (BCNF)

Given Relation: EMP (EmpID, EmpName, DeptID, DeptName, DeptLocation)

Process:

1. Identify Functional Dependencies (FDs): Based on real-world assumptions:
   • EmpID → EmpName, DeptID (An employee has one name and belongs to one department)
   • DeptID → DeptName, DeptLocation (A department has one name and one location)

   From these FDs, we can see there is a transitive dependency: EmpID → DeptID and DeptID → DeptName, DeptLocation.

2. Find the Candidate Key: Based on the given FDs, the only candidate key is {EmpID} .
3. Check Normal Forms:
   • 1NF: The relation is in 1NF as all attributes are atomic.
   • 2NF: The relation is in 2NF because there are no partial dependencies (the primary key, EmpID, is a single attribute).
   • 3NF: The relation is not in 3NF because there is a transitive dependency: EmpID → DeptID → {DeptName, DeptLocation} . Here, DeptID is not a superkey, and DeptName, DeptLocation are non-prime attributes.
4. Decompose into BCNF: For a relation to be in BCNF, for every non-trivial FD X → Y, X must be a superkey.
   • The FD DeptID → DeptName, DeptLocation violates BCNF because DeptID is not a superkey of the original EMP relation.

   To eliminate this violation, we decompose the relation into two:

   • EMP_DETAILS ( EmpID , EmpName, DeptID) (Here, DeptID will be a foreign key referencing DEPT_DETAILS.)
   • DEPT_DETAILS ( DeptID , DeptName, DeptLocation)

Now, both resulting relations are in BCNF. In

EMP_DETAILS

, the only FD is EmpID → EmpName, DeptID, where EmpID is the key. In

DEPT_DETAILS

, the FD is DeptID → DeptName, DeptLocation, where DeptID is the key. The normalization is complete.

(c) Log-Based Recovery and Checkpointing

Log-based recovery is a technique that guarantees the Atomicity and Durability properties of transactions, especially in the event of a system failure. It works by keeping a log file that records all transaction operations that modify the database.

Types of Log Records:

• <T, start>: Transaction T has started.
• <T, X, V1, V2>: Transaction T has changed the value of data item X from V1 (old value) to V2 (new value).
• <T, commit>: Transaction T has completed successfully.
• <T, abort>: Transaction T has failed.

Checkpointing: Checkpointing is a mechanism to reduce the time needed for recovery. During checkpointing, the DBMS periodically performs the following actions:

1. Writes all modified data blocks from memory (buffer) to the disk.
2. Writes a <checkpoint> record to the log file.

This means that during recovery, the system doesn’t need to scan the entire log; it can start from the last checkpoint.

Recovery Process with an Example: Consider the following log sequence before a system crash: … <T1, start> <T1, A, 500, 400> <T1, commit> <checkpoint> <T2, start> <T2, B, 1000, 1200> <T3, start> <T3, C, 700, 600> <T3, commit> <T2, D, 200, 300> — System Crash —

Recovery Steps:

1. The system finds the last <checkpoint> record.
2. It creates two lists: an Undo-list and a Redo-list .
   • All transactions that started after the checkpoint (T2, T3) are placed in the Undo-list.
   • Transactions for which a <commit> record is found (T3) are moved from the Undo-list to the Redo-list.
   • Thus, Undo-list = {T2} , Redo-list = {T3} .
3. REDO Phase: The operations of all transactions in the Redo-list (T3) are reapplied to ensure their changes are durable. So, the value of C is set to 600.
4. UNDO Phase: The operations of all transactions in the Undo-list (T2) are rolled back to erase their incomplete changes. So, the value of D is reverted to 200 and B is reverted to 1000.

(d) Relational Algebra Expressions

Given Relation: STUDENT (SID, SName, Major, AdvisorID)

(i) Get names of students majoring in ‘CS’: This operation will first select the students majoring in ‘CS’ and then project their names. Π _SName (σ _{Major=’CS’} (STUDENT))

• σ _{Major=’CS’} (STUDENT) : This selects all tuples (rows) from the STUDENT table where the ‘Major’ column has the value ‘CS’.
• Π _SName (…) : This projects (displays) only the ‘SName’ column from the resulting tuples.

(ii) List all students advised by advisor ‘A077’: This operation will select all records from the STUDENT table where the advisor ID is ‘A077’. σ _{AdvisorID=’A077′} (STUDENT)

• σ _{AdvisorID=’A077′} (STUDENT) : This selects all tuples from the STUDENT table where the ‘AdvisorID’ column has the value ‘A077’. This operation will return all details (SID, SName, Major, AdvisorID) of the qualifying students.

Q2. (a) Explain 2-Phase Locking (2PL) protocol with a proper schedule. How does it help in achieving serializability? (b) With the help of a neat diagram, explain the structure of client-server database management systems. Mention any two advantages of this architecture.

Ans.

(a) 2-Phase Locking (2PL) Protocol

The Two-Phase Locking (2PL) protocol is a concurrency control method used in DBMS to guarantee serializability. It ensures that transactions do not interfere with each other in a way that leads to an inconsistent database state. The protocol operates in two distinct phases for any transaction:

1. Growing Phase (or Expanding Phase): In this phase, a transaction can obtain new locks (both shared/read and exclusive/write) on data items, but it cannot release any lock.
2. Shrinking Phase (or Contracting Phase): In this phase, a transaction can release its existing locks, but it cannot obtain any new locks.

The point where a transaction acquires its final lock and begins releasing locks is called the lock point . 2PL guarantees serializability by ensuring that the order of lock points of concurrent transactions corresponds to a valid serial schedule.

Example of a Proper Schedule under 2PL: Let’s consider two transactions, T1 and T2, accessing data items A and B.

Schedule allowed by 2PL:

• T1: lock-X(A) (Growing Phase starts for T1)
• T1: read(A)
• T1: A = A – 50
• T1: write(A)
• T2: lock-X(B) (T2 requests lock on B, granted as T1 doesn’t hold it. Growing Phase starts for T2)
• T2: read(B)
• T2: B = B + 50
• T2: write(B)
• T1: lock-X(B) (T1 requests lock on B, but it is held by T2. T1 must wait)
• T2: unlock(B) (Shrinking Phase starts for T2)
• T1: (Lock on B is granted to T1 now. T1 is still in its growing phase)
• T1: read(B)
• T1: B = B – 50
• T1: write(B)
• T1: unlock(A) (Shrinking Phase starts for T1)
• T1: unlock(B)

This schedule is serializable (equivalent to T2 then T1) because 2PL prevented a conflicting access until one transaction released the lock.

How it achieves serializability: 2PL ensures that the precedence graph for any schedule it permits is acyclic. If a transaction T1 has a conflict with T2 (e.g., T1 reads an item that T2 later writes), T1 must have released its lock before T2 could acquire it. This forces an ordering on the lock points of T1 and T2, which prevents cycles in the precedence graph. An acyclic precedence graph is the definition of a conflict-serializable schedule.

(b) Client-Server DBMS Architecture

The client-server architecture is a widely used model for database systems, where the overall functionality is split between two types of components: clients and a server. These components communicate over a network.

Structure:

1. Client (Front-End): This is the application or program that the end-user interacts with. It is responsible for the user interface and some application logic. The client’s primary role is to accept user input, formulate a request (e.g., an SQL query), send it to the server, and then present the result received from the server to the user. Clients are often called “fat” if they contain significant application logic or “thin” if they primarily handle the user interface.
2. Server (Back-End): This is the core of the DBMS. It runs on a powerful machine and is responsible for managing the database. Its tasks include:
   • Processing queries and data manipulation requests from clients.
   • Enforcing data integrity and security constraints.
   • Managing concurrent access by multiple clients.
   • Performing backup and recovery operations.
   • Optimizing query execution.

Diagram: (Students should draw a diagram showing multiple Client machines connected via a Network to a single Server machine. The Server machine is shown with the DBMS software and the physical Database.)

 [Client 1] -----\ [Client 2] ------\ [ Network ] ------ [ Server ] [Client 3] ------/ | | [ DBMS Software ] | [ Database ]

Advantages of Client-Server Architecture:

1. Centralized Data Management and Security: Data is stored and managed in a central location (the server). This makes it easier to enforce security policies, maintain data integrity, and perform backups. All access to data is controlled by the server DBMS, providing a single point of control.
2. Improved Scalability and Performance: The architecture is highly scalable as more clients can be added without significantly affecting the server. The powerful server handles all heavy data processing, freeing up the client machines’ resources. This division of labor improves overall system performance, especially for complex queries.

Q3. (a) Given the transaction schedule below, determine whether it is conflict serializable: T1: R(A), W(A), R(B), W(B); T2: R(A), W(A), R(B), W(B). Explain your answer using a precedence graph. (b) Create a comparative table to explain the differences between Heap file, Sequential file and Hashed file organizations. (c) Briefly explain any two differences between database integrity and security with relevant examples.

Ans.

(a) Conflict Serializability

The question lists the operations within each transaction but does not provide a single interleaved schedule. To determine conflict serializability, we must assume an interleaved schedule of these operations. A common problematic interleaving is as follows:

Assumed Schedule (S): R1(A), R2(A), W1(A), W2(A), R1(B), R2(B), W1(B), W2(B)

To determine if this schedule is conflict serializable, we need to find all conflicting operations and draw a precedence graph.

Conflicting Operations: Two operations from different transactions conflict if they access the same data item and at least one of them is a write operation.

Let’s identify the conflicts in schedule S:

1. R2(A) and W1(A): These conflict. Since R2(A) occurs before W1(A), we draw an edge from T2 to T1. (T2 → T1) .
2. W1(A) and W2(A): These conflict. Since W1(A) occurs before W2(A), we draw an edge from T1 to T2. (T1 → T2) .
3. R2(B) and W1(B): These conflict. Since R2(B) occurs before W1(B), we draw an edge from T2 to T1. (T2 → T1) .
4. W1(B) and W2(B): These conflict. Since W1(B) occurs before W2(B), we draw an edge from T1 to T2. (T1 → T2) .

Precedence Graph: The precedence graph has nodes for each transaction (T1 and T2). We draw an edge from Ti to Tj if an operation in Ti conflicts with and occurs before an operation in Tj.

• From conflict (1) and (3), we have an edge: T2 → T1 .
• From conflict (2) and (4), we have an edge: T1 → T2 .

The resulting graph has two nodes, T1 and T2, with an edge from T1 to T2 and another edge from T2 to T1. (T1) <–> (T2)

Conclusion: The precedence graph contains a cycle (T1 → T2 → T1). A schedule is conflict serializable if and only if its precedence graph is acyclic. Since our graph has a cycle, the given schedule is not conflict serializable .

(b) Comparison of File Organizations

Feature	Heap File Organization	Sequential File Organization	Hashed File Organization
Record Ordering	No specific order. Records are placed in the next available space, usually at the end of the file.	Records are physically ordered based on the value of a specific field, called the search key.	Records are placed at a location determined by a hash function applied to a key field.
Access Method	Full file scan (linear search) is required to locate a specific record.	Efficient for reading records in order. Can use binary search for random access if the key is known.	Direct access. The hash function computes the address of the block containing the record.
Search Time	Slow, proportional to the file size. O(n).	Fast for range queries and sequential access. O(log n) for random access via binary search.	Very fast on average for random access. O(1) if there are no collisions.
Insertion	Very fast. The new record is simply appended to the end of the file.	Slow and complex, as it requires finding the correct position and potentially shifting existing records to maintain order.	Fast, as the location is calculated directly. However, collisions (multiple keys hashing to the same address) can slow it down.
Deletion	Requires a search to find the record, then marking it as deleted. Can lead to unused space.	Requires a search and then shifting records to fill the gap, or using pointers.	Fast. Find the record via hash and remove it. Can be complex if chaining is used for collision resolution.

(c) Database Integrity vs. Security

Database integrity and security are both crucial for data management, but they address different concerns.

Difference 1: Goal and Focus

• Database Integrity: Focuses on the correctness, consistency, and accuracy of data. It aims to prevent accidental data corruption by users who have legitimate access. It is about the quality of the data itself. Example: An entity integrity constraint ensures that the primary key of a table (e.g., `StudentID`) can never be NULL. This guarantees that every record is uniquely identifiable.
• Database Security: Focuses on the protection of data from unauthorized access, modification, or destruction . It aims to prevent malicious or unauthorized actions, whether intentional or not. It is about controlling who can access or change the data. Example: Using the `GRANT` and `REVOKE` commands in SQL to give a user `SELECT` permission on the `Employees` table but not `UPDATE` permission on the `Salary` column. This prevents an unauthorized user from viewing or changing sensitive salary data.

Difference 2: Implementation Mechanisms

• Database Integrity: Implemented primarily through constraints defined in the database schema (DDL). Example: A referential integrity constraint (FOREIGN KEY) ensures that a value entered in a column of one table (e.g., `DeptID` in the `Employees` table) must exist in the primary key column of a related table (e.g., `DeptID` in the `Departments` table). This prevents “orphan” records.
• Database Security: Implemented through a variety of mechanisms, including authentication (passwords), authorization (user privileges and roles), encryption , and auditing. Example: Encrypting a column containing credit card numbers. Even if an attacker gains access to the database file (bypassing authorization), they cannot read the sensitive data without the decryption key.

Q4. Write short notes on the following: (a) Referential Integrity (b) Data fragmentation in distributed database (c) Boyce-Codd Normal Form (BCNF) (d) Role of the Database Administrator (DBA)

Ans.

(a) Referential Integrity Referential integrity is a fundamental concept in relational databases that ensures consistency between related tables. It is a constraint that applies to a foreign key. A foreign key is a column (or a set of columns) in one table that refers to the primary key of another table. The referential integrity constraint states that the value of the foreign key in any given row of the “child” table must match a value in the primary key of the “parent” table, or the foreign key value must be NULL. This constraint prevents “orphan records”—records in the child table that have no corresponding record in the parent table. For example, if an `Orders` table has a `CustomerID` foreign key that references the `Customers` table, referential integrity ensures that you cannot create an order for a non-existent customer. Similarly, it controls what happens if a parent record is deleted or its primary key is changed. Actions like `ON DELETE CASCADE` (delete child records automatically) or `ON DELETE SET NULL` (set foreign key to NULL) can be defined to handle such cases.

(b) Data Fragmentation in Distributed Database Data fragmentation is a technique used in distributed database systems to divide a database into smaller, more manageable pieces called fragments. These fragments can then be stored at different physical locations (sites) across a network. The primary goal is to improve performance, reliability, and availability by storing data closer to where it is most frequently used. There are three main types of fragmentation:

1. Horizontal Fragmentation: The table is split into subsets of rows (tuples). Each fragment contains all columns but only a specific set of rows, based on a condition or predicate. For example, a `Customers` table could be fragmented by city, with customer data for Delhi stored at the Delhi site and data for Mumbai at the Mumbai site.
2. Vertical Fragmentation: The table is split into subsets of columns (attributes). Each fragment contains all rows but only a specific set of columns, plus the primary key which is repeated in all fragments to allow for reconstruction of the original table. For example, an `Employee` table could be split into `(EmpID, Name, Address)` and `(EmpID, Salary, PerformanceRating)`.
3. Hybrid (Mixed) Fragmentation: This is a combination of horizontal and vertical fragmentation. A table is first fragmented vertically, and then one or more of the vertical fragments are further fragmented horizontally.

The DBMS must manage these fragments transparently, allowing users to query the data as if it were a single, centralized database.

(c) Boyce-Codd Normal Form (BCNF) Boyce-Codd Normal Form (BCNF) is a higher, stricter level of database normalization than the Third Normal Form (3NF). A relation is in BCNF if and only if for every one of its non-trivial functional dependencies (FDs) X → Y , the determinant X is a superkey of the relation. A superkey is a set of attributes that can uniquely identify any row in the table. While every relation in BCNF is also in 3NF, a relation in 3NF is not necessarily in BCNF. The difference arises in specific situations, typically involving multiple overlapping candidate keys. BCNF was designed to handle anomalies that 3NF does not. If a relation is not in BCNF, it means there is a dependency where a non-key attribute (or a part of a composite key) determines another attribute, which can lead to redundancy and update anomalies. To bring a relation into BCNF, it is decomposed into smaller relations that satisfy the BCNF condition, a process that, unlike 3NF decomposition, may not always preserve all functional dependencies.

(d) Role of the Database Administrator (DBA) The Database Administrator (DBA) is a specialized IT professional responsible for the management, maintenance, performance, and reliability of a database management system (DBMS). The DBA’s role is critical in ensuring that an organization’s data is both accessible and secure. Key responsibilities include:

• Schema Definition and Management: Designing and implementing the database schema, and modifying it as application needs change.
• Security Administration: Defining and implementing access policies and procedures. This involves creating user accounts, granting and revoking privileges, and preventing unauthorized access.
• Backup and Recovery: Developing, implementing, and testing a backup and recovery plan to protect the database against data loss from hardware failure, human error, or disasters.
• Performance Monitoring and Tuning: Proactively monitoring the database’s performance, identifying bottlenecks (like slow queries), and optimizing the system through index management, query rewriting, and hardware configuration.
• Installation and Upgrades: Installing new DBMS software and applying patches or upgrades to keep the system up-to-date.
• Data Archiving: Moving old data that is no longer actively used to an archive to maintain performance.

In essence, the DBA acts as the guardian of the database, bridging the gap between the technology, the data, and the users.

Q5. Differentiate between the following: (a) Flat File Systems vs. Relational Database Systems (b) Data Definition Language (DDL) vs. Data Manipulation Language (DML) (c) Logical DBMS Architecture vs. Physical DBMS Architecture (d) Centralized vs. Distributed Database

Ans.

(a) Flat File Systems vs. Relational Database Systems

• Structure: A Flat File System stores data in individual files (e.g., .txt, .csv) with little to no structure or relationship between them. A Relational Database System (RDS) stores data in highly structured tables with predefined schemas and well-defined relationships between them using keys.
• Data Redundancy: Flat files often lead to high data redundancy and inconsistency because the same data may be duplicated across multiple files. RDS minimizes redundancy through normalization.
• Data Access: Accessing and querying data in flat files is limited and often requires writing custom programs to parse the file. RDS provides a powerful, standardized query language (SQL) for complex data retrieval and manipulation.
• Concurrency and Integrity: Flat file systems offer little or no support for concurrent access by multiple users or for enforcing data integrity rules. RDS provides sophisticated mechanisms for concurrency control (e.g., locking) and enforces data integrity through constraints (e.g., PRIMARY KEY, FOREIGN KEY).

(b) Data Definition Language (DDL) vs. Data Manipulation Language (DML)

• Purpose: DDL (Data Definition Language) is used to define and manage the structure of database objects. It deals with the database schema. DML (Data Manipulation Language) is used to access and manipulate the data stored within those objects.
• Commands: Common DDL commands include `CREATE` (to create tables, views, indexes), `ALTER` (to modify the structure), and `DROP` (to delete objects). Common DML commands include `SELECT` (to retrieve data), `INSERT` (to add new data), `UPDATE` (to modify existing data), and `DELETE` (to remove data).
• Impact: DDL statements affect the metadata (the “data about data”) and are often auto-committed, meaning they cannot be easily rolled back. DML statements affect the actual data rows in the tables and are usually part of a transaction that can be committed or rolled back.

(c) Logical DBMS Architecture vs. Physical DBMS Architecture These terms refer to different levels of the ANSI-SPARC three-schema architecture.

• Logical DBMS Architecture (Conceptual Schema): This level describes the overall structure of the entire database for a community of users. It defines all the entities, attributes, relationships, and constraints, hiding the details of the physical storage structures. It focuses on what data is stored and how the data relates to other data. This is the level at which database designers and DBAs work when creating the overall data model.
• Physical DBMS Architecture (Internal Schema): This is the lowest level of abstraction, describing how the data is physically stored on storage devices. It deals with file organization (e.g., heap, B-tree), indexing methods, data compression, encryption, and the exact location of data on disk. Its goal is to optimize performance and storage utilization. This level is primarily the concern of the DBA and is hidden from application programmers and end-users.

(d) Centralized vs. Distributed Database

• Location of Data: In a Centralized Database , the entire database (data, DBMS software, and processing logic) resides on a single computer or at a single site. In a Distributed Database , the data is spread across multiple physical sites connected by a network. The data may be fragmented (split) and/or replicated (copied) across these sites.
• Reliability and Availability: A centralized system has a single point of failure; if the central site goes down, the entire system is unavailable. A distributed system offers higher reliability and availability because the failure of one site does not necessarily bring down the entire system; data may be accessible from other sites (especially if replicated).
• Complexity and Management: Centralized databases are much simpler to design, manage, and secure. Distributed databases are highly complex, as they require sophisticated mechanisms to manage data distribution, process queries across multiple sites, maintain consistency of replicated data, and handle distributed concurrency control.
• Performance: A centralized database may become a bottleneck for geographically dispersed users. A distributed database can provide better performance by placing data closer to the users who access it most frequently (data locality), reducing network latency.